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
