Dynamic SQL & ANSI_NULL/WARNING error 1

[skicamel]

I receive this error when running this script (tablenames have been changed to protect the innocent)

declare @dbname varchar(50)
declare @id varchar(10)
set @dbname = 'database1'
set @id = '1001'
declare @sql nvarchar(3000)
set @sql = 'select top 5 table1.id, value
from server.'+@dbname+'.dbo.table1 table1,
server.'+@dbname+'.dbo.table2 table2
where table2.id = table1.otherid
and table1.id = '+@id+' '

exec sp_sqlexec @sql

I'm receiving this error:

Server: Msg 7405, Level 16, State 1, Line 1
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.

After throwing in

set ansi_nulls on
go
set ansi_warnings on
go

or variations thereof before the script, the same error is still being returned. Any ideas, folks?

 

[skuhlman]

It's a little fuzzy to me, but since no one else seems to be answering you, I'll take a stab. I think that the SET ANSI_NULLS ON and SET ANSI_WARNINGS ON are correctly configuring your current session. I belive that the EXEC SP_SQLEXEC @SQL is working in a different environment. If SP_SQLEXEC were a stored procedure you had written, you could just add the ANSI setting commands to the beggining of the stored procedure:

Set ANSI_NULLS on
set ANSI_WARNINGS on
GO
CREATE...

By doing this, the ANSI settings environment is established prior to the stored procedure being created. Once it's created, it retains the environment that was there at creation time.

However, since it's a system stored procedure, I'm not sure that you can make changes like that. I'm fairly new to this stuff, so I may not have the best solution, but you may want to write a stored procedure to run at boot time for the server which would change SQL's default environment settings.

Hope this helps.

 

[skicamel]

I just read something not long ago about ANSI settings & stored procedures and didn't even make the connection. Yes, that helps immensely. I changed the execution to

exec (@sql)

and now it's working beautifully.

Thanks!