April 30, 2003 at 6:21 am
I am getting this error while trying to run sp's with a linked server.
"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."
I understand that I need to SET ANSI_NULLS ON and SET ANSI_WARNINGS ON prior to creating the stored procedure, but my problem is a little more complicated.
I have 3 procedures. Procedure 1 and 2 are performing updates and comparisons between 2 servers, 1 of which is linked. If I create the procedures and run them separately I do not get errors. My error occurs when I utilize Procedure 3 to fire off the other 2. See Code below:
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
Create Proc spc_3
As
exec proc_1
exec proc_2
When I try to run procedure 3 I get the error above. I need to figure out how to give this 3rd procedure the ability to fire off the other 2 without getting that damn error.
I'm very frustrated and really need to get this project done. Can anyone help?
Thanks.
April 30, 2003 at 10:30 am
Have you tried setting them on the linked server for connections? (providing it does cause problems elsewhere!)
Far away is close at hand in the images of elsewhere.
Anon.
April 30, 2003 at 4:57 pm
quote:
Have you tried setting them on the linked server for connections? (providing it does cause problems elsewhere!)How would I do that? I am running the script from the main server. The procedures are created on the main server so while I created them I set the ansi_nulls and warnings prior to creating the procedure.
May 2, 2003 at 2:25 am
In EM right click on the server (the one you are linking to) and select properties. Select the Connections tab, click on ANSI warnings and ANSI nulls (to put tick in box). Click on Apply and then OK.
Note this will set these options on ALL connections made to that server.
This should overcome your problem as it is the connection to the linked server that is causing your problem not how or where the procs are created.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply