Help with Error 7405

[marktraynor]

Hi

I am trying to update a table on db that is a linked server. I can run the TSQL from the query analyser and the code works great however when I try and create a stored procedure with the same code I receive the following error.

Error 7405 Heterogenous 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.

However when I run DBCC USEROPTIONS on both the local box and the linked server both return ansi_nulls and ansi_warnings as SET.

Here is the code
UPDATE [links].Orders2k.dbo.Goupdates
SET Use = @d1
WHERE userid = @userid

Thanks in advance
Cheers
Mark

[MAK]

It uses your database's settings.

try this

set ansi_nulls xxxx
set ansi_warnings xxxx
SET ARITHABORT ON

UPDATE [links].Orders2k.dbo.Goupdates
SET Use = @d1
WHERE userid = @userid

[marktraynor]

Hi

I tried

set ansi_nulls on
set ansi_warnings on
SET ARITHABORT ON

UPDATE [links].Orders2k.dbo.Goupdates
SET Use = @d1
WHERE userid = @userid

However I still receive the same errors, any other ideas?

Cheers
Mark

[skhanal]

Try by setting on this

set ansi_nulls on


Refer to http://support.microsoft.com/default...b;en-us;296769

[marktraynor]

Thanks that did the trick ;o)

Cheers
Mark