servers and sp_executeSQL

Hi,

I am trying to do something like the following within a Stored Procedure but have also tried it on its own within a batch:

--
go
Declare @.MySQL nvarchar(500)
@.MySQL = 'Select * from openquery(borders, ''Select * from

dba.organise'')'

exec sp_executeSQL @.MySQL
go
--

then I get Error 7405 'Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection...' I tried setting these in a SQL batch before the one above. Various sources say that you should set these before you create procedure but as you can see above I am not doing this within a create procedure - is it something to do with the sp_executeSQL ?? Help!!!EXEC and sp_executeSQL run on seperate threads so include the set statments in your string

@.MySQL = 'set ANSI_NULLS on
set ANSI_WARNINGS on
Select * from openquery(borders, ''Select * from dba.organise'')'
exec(@.MySQL)

Also, I have found that including the database name in the table reference will eliminate headaches.|||Thanks Paul - What I eventually did was this:

set ansi_nulls on
set ansi_warnings on
go
create procedure ....

... putting it in the SQL statement worked in Query Analyzer but not when it was within the stored procedure.

Thanks for the quick reply!
Regards

Anna|||I would suggest doing the SETs inside the sp, that way if someone extracts the code from your server and recompiles you will still have the correct environment for you linked server query|||Hi Paul,

I did try that but I couldn't seem to get that to work.

Cheers

Anna