Adnan (ad**********@y ahoo.ca) writes:
I have one stored procedure on SQL 6.5 and retrieving data from SQL
2000. I have defined a linked server on SQL 6.5. But
I am getting following error:-
"Heterogene ous 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 dropped and re-create the stored procedure after adding the SET
ANSI_NULLS & SET ANSI_WARNINGS ON in stored procedure but still getting
this error. I have created this sp on ISQL/W not in Enterprise Manager.
Can anyone please let me know, how to fix this problem.
The setting of ANSI_NULLS is saved with the procedure, why it does
not help setting ANSI_NULLS within the procedure.
ANSI_NULLS is on by default with most interfaces - but not if you use
DB-Library, and the 6.5 tools uses DB-Library. If you insist on using
6.5 tools, be sure to always include this:
SET ANSI_DEFAULTS ON
SET IMPLICIT_TRANSA CTIONS OFF
SET CURSOR_CLOSE_ON _COMMIT OFF
then you get the same settings as in the SQL 2000 tools.
Certainly far more easier is to use Query Analyzer. (You mentioned
Enterprise Manager. If you mean the 6.5 tool, it has the same issue
as ISQL/W. It you mean EM 2000, this is a poor tool for maintaining
stored procedures.)
--
Erland Sommarskog, SQL Server MVP,
es****@sommarsk og.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp