linked servers require ANSI_NULLS and ANSI_WARNING | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

linked servers require ANSI_NULLS and ANSI_WARNING

When trying to access a linked server in a stored procedure, the syntax checker fails the query with the result… —————————
Microsoft SQL-DMO (ODBC SQLState: 42000)
—————————
Error 7405: 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.
—————————
OK
—————————
I have gone onto both the source and destination linked server, and set ANSI_WARNINGS and ANSI_NULLS to ON. Why am I still getting this error ?
Not ‘in’ the procedure.
Use these settings while creating the procedure SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE xxx(…
GO Satya SKJ
Many thanks satya!
I didnt realise the SP had to be recreated after setting those ansi options.
BOL referred that SET ANSI_NULLS should be set to ON for executing distributed queries. HTH Satya SKJ
]]>