You can set compatibility level per database. You have migrated this database from SQL 2000 so its compatibility level stayed at 80. You can change it using sp_dbcmptlevel stored proc....

What is you want to achieve?

Piotr

Yes, but the compatibility level will have to stay below 90. This syntax will be depreciated in future.

create table t1(a int)

create table t2(b int)

go

select a from t1, t2 where t1.a...

also, check if the click handler doesn't swallow exceptions. it's unbelievably common to have empty catch block.

Piotr

Is that what you have in code?

CREATE Procedure usp_Run_all_4_Procedures

@FolderDate datetime

@Servicea string

@acYearStart datetime

@Serviceb string

AS

EXEC usp_Run_all_4_Procedures

It should be

CREATE Procedure usp_Run_all_4_Procedures

(

@FolderDate datetime,

@Servicea string,

@acYearStart datetime,

@Serviceb string

)

AS

EXEC...

Please send sample table schemas, sample data and desired result. You must agree that there is no precise answer to your question.

Piotr

Hi,

Read about linked servers or openrowset function. There are examples how to connect to excel.

Piotr

It would help if you sent the DDL for your tables. I thought you want to update email in [name] table from [name_address] as this third party app is using...

By default OPENROWSET is disabled. You have to go to Surface Area Configuration tool and enable it.

Piotr

You can't create local temporary tables using sp_executesql

the reason is that local temp tables (with single # sign) are destroyed when current session is disconnected. sp_executesql creates a new session...

is this what you are looking for?

select * from sys.syslogins

or

select * from sys.server_principals

Piotr

Duh, of course, exec. My mind is focused on weekend now 🙂

Thanks!

On the other hand, Stef is also using exec:

N'exec @ValueOUT=@CustomFunctionName @InPtHt, @InHtUnits'

wouldn't it be better to use set/select?

N'set...

Adam, could you explain why there is no plan reuse in the latter case?

Piotr

usually you put 'select' before function name.

SET @strExec = 'select ' + @FunctionName + ' @InPtHt, @HtUnits'

if this is a scalar function, declare output parameter and pass it to sp_executesql

Piotr

You have to use linked server. Read in BOL about them.

Piotr