Error: Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection


TechKnowledge Content


Question:
I have an Access DB and a SQL table that store information. Under the Linked Server features of SQL 7.0, I've allowed SQL to see the Access DB as just another SQL database. I've also written a stored procedure that copies records from the Access DB to the SQL table. Running from ISQL/Query Analyzer, the stored procedure runs and works correctly. Calling the stored procedure from Dexterity produces the following error message from SQL Server:
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.Can I even run this stored procedure from Dexterity?


Answer:
Yes but we'll have to modify the stored procedure slightly. The reason it fails is that in order to read SQL tables properly in Dexterity, those options must be turned OFF so calling stored procedures in Dexterity issues those commands before running the stored procedure. The user must make sure data that violates these rules is not introduced into the SQL table. In the sample below, the table_access is the access table. The table_test is the SQL table.


if exists (select * from sysobjects where id =

object_id('dbo.access_update') and sysstat & 0xf = 4)

drop procedure dbo.access_update

GO


create procedure dbo.access_update @O_SQL_Error_State int = NULL output


as


INSERT INTO access_table ( DOCNUMBR, DOCAMNT )

SELECT access_test.DOCNUMBR, access_test.DOCAMNT

FROM access_test


GO


We can modify the stored procedure to set our warnings on like this:


if exists (select * from sysobjects where id =

object_id('dbo.access_update') and sysstat & 0xf = 4)

drop procedure dbo.access_update

GO


create procedure dbo.access_update @O_SQL_Error_State int = NULL output


as


set ANSI_NULLS ON

SET ANSI_WARNINGS ON


exec access_table2 ( DOCNUMBR, DOCAMNT )


GO


In this case, the stored procedure inherits the attributes of what calls it. For this situation,Dexterity calls theaccess_tableprocedure which turns on the ANSI_NULLS & ANSI_WARNINGS and then calls the real stored procedure and passes in our parameters.


This article was TechKnowledge Document ID:10011