I have create a stored procedure on SQL server 2000 Server using the code...
SET ANSI_NULLS ON
GO
CREATE PROCEDURE get_XXX.... AS...

However when I execute this in Query Analyzer I get the error "Server: Msg 7405, Level 16, State 1, Procedure get_XXX, Line 71. 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."
When I go to Line 71 it is the first SELECT statement using my Linked Server (another SQL server 2000 Server). So somehow I have to get the ANSI_NULLS /WARNINGS connected to my linked server.

I can't see where I'm going wrong. I put SET ANSI_NULLS ON at the beginning of the sp when i created it and it successfully created it error-free. I have dropped & recreated the sp many times to no avail.

You don't put them in your procedure. You put the two set commands BEFORE the create procedure statement in the create script and then BEFORE you call the procedure you have to set the options for the connection.

0

RobertEnglishAuthor Commented: 2008-02-04

"You put the two set commands BEFORE the create procedure statement in the create script and then BEFORE you call the procedure you have to set the options for the connection."
Just given that a go and I think it may be the answer. I'll do some more rigorous testing and add another comment / accept a solution later