Linked Server - Error 7405: Heterogeneous queries

SET_ANSI_NULLS for a stored procedure is taken from the settings that were active at creation time. You may need to re-create the stored procedure, ensuring that you have executed SET ANSI_NULLS ON before executing the CREATE PROCEDURE (in the same session, but a separate Batch). You can check the setting by using the OBJECTPROPERTY() function against your stored proc.

The ANSI_WARNINGS state is determined by the current setting when the procedure is executed. The ODBC Driver and OLE DB Provider automatically set this ON by default.

Can please explaine little clearer or give an example?



CREATE procedure Test


as


SET ANSI_NULLS ON




EXEC ('SELECT COUNT(*) FROM XX.YY.DBO.TABLE1')

GO

wHEN i EXECUTE THIS from query analyzer I still get an 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.


Thank you

You do the Set before executing the Create Procedure.  This, for example, should work:

SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
CREATE PROCEDURE MYPROC()...

set ANSI_NULLS ON
SET ANSI_WARNINGS ON
CREATE procedure Test

as

EXEC ('SELECT COUNT(*) FROM XX.YY.DBO.TABLE1')

go

It doesn't accept this proc.  I can't place on the top these statements
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON


Can you send me NorthWind db sample procedure that works?

Thank you

Thank you very much