Heterogeneous queries

Hi Guys,

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:-

"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."

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.

Thanks
AdnanAdnan (adnanjamil58@.yahoo.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:-
> "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."
> 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_TRANSACTIONS 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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog (esquel@.sommarskog.se) writes:
> The setting of ANSI_NULLS is saved with the procedure, why it does
> not help setting ANSI_NULLS within the procedure.

A clarification: the setting of ANSI_NULLS that applies for a stored
procedure, is the setting that was in effect when you created the procedure.
The rest below applies as before:

> 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_TRANSACTIONS 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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp