I have create a stored procedure on SQL server 2000 Server using the code...
SET ANSI_NULLS ON
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.