Error: Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options

[Jim Fox]

Hi,

 

SQLServer 2000, using an OPENDATASOURCE command within a stored procedure to access data on another Server running 2000.  I get the following error, when I exececute the Stored Procedure in Query Analyzer:

 

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.

Interestingly, when I issue the identicl select statement within Query Analyzer, it works fine.

 

I tried issuing a "Set ANSI_NULLS OFF" etc commands prior, to no avail. 

 

THanks for any help.

Jim

[Scott Morris]

Review the information in BOL regarding "set ansi_nulls" - pay special
attention to the information about stored procedures. Then review the notes
for "create procedure" - it reiterates the previous point and adds some
additional information. Then - go fix your procedure. Note that using EM
to do this makes the process that much more difficult, since it tends to
hide important details (like this). Instead, use QA and a script to create
the procedure. Of course, you should be using scripts of some sort since
all code for the database (schema, stored procedures, UDFs, triggers, etc)
are as important to the entire system as your application code.

BTW - you want to set ansi_nulls ON, not OFF. Generally, something that is
"set" means it is set "on".

[Sue Hoegemeier]

Generally the error is due to needing to set the properties
when you create the stored procedure. Try recreating your
stored procedure using:
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE YourStoredProc...etc.

-Sue

[Jim Fox]

Thanks - Much appreciated!
"Scott Morris" <bo...@bogus.com> wrote in message
news:%23VbpjNk...@TK2MSFTNGP04.phx.gbl...

[Geoff Varosky]

Along these lines, I am calling a trigger that runs an
insert/update/delete on a linked server table, and am running into the
same issues. However, setting ANSI_NULLS or ANSI_WARNINGS in the
trigger itself does not help out at all...

I've been on the phone with MS all day, and am looking for a fresh
perspective...

My trigger is this:

CREATE TRIGGER opsCompany_Insert ON [dbo].[RM00101]
FOR INSERT
AS

SET ANSI_DEFAULTS ON

-- SET THE DB NAME / DO THIS FOR EACH COMPANY DB --
DECLARE @CompanyDB CHAR(5)
SELECT @CompanyDB = (SELECT 'TWO')

-- Set Company Number
DECLARE @CompanyNumber CHAR(15)
SELECT @CompanyNumber = (SELECT CUSTNMBR FROM INSERTED)

-- Set Company Name
DECLARE @CompanyName CHAR(65)
SELECT @CompanyName = (SELECT CUSTNAME FROM INSERTED)

-- Set Currency ID
DECLARE @CurrencyID CHAR(15)
SELECT @CurrencyID = (SELECT CURNCYID FROM INSERTED)

-- Set GovernmentID
DECLARE @GovernmentID CHAR(25)
SELECT @GovernmentID = (SELECT TXRGNNUM FROM INSERTED)

-- DEX_ROW_ID
DECLARE @MstrID INT
SELECT @MstrID = (SELECT DEX_ROW_ID FROM INSERTED)

-- Push to Cranberry
INSERT INTO SQLSVR.TESTDATA.dbo.Company (GPCompanyID, CompanyNumber,
CompanyName, CurrencyID, GovernmentID, CompanyDB, AddedBy, AddedOn)
VALUES (@MstrID, @CompanyNumber, @CompanyName, @CurrencyID,
@GovernmentID, @CompanyDB, user, getdate())

I can run the trigger fine to a local database, but to the linked
server, I get the same 'Heterogeneous' error... I ahve set it up from
QA with

SET ANSI_NULLS, ANSI_WARNINGS ON
GO
Create Trigger ....

And also setting it within the trigger right after 'AS'

However still no luck...

Any thoughts?

Thanks!

[Scott Morris]

> Along these lines, I am calling a trigger that runs an
> insert/update/delete on a linked server table, and am running into the
> same issues. However, setting ANSI_NULLS or ANSI_WARNINGS in the
> trigger itself does not help out at all...

These are connection level settings - for the most part. Stored procedures
have their own wrinkle to this. Ultimately, the issue is the same. You
must use the appropriate connection-level settings for this architecture to
work. Ideally, your client application should be designed to enforce the
appropriate settings. If you can't do that, then the only other option
that I can see is to put your logic for accessing the remote DB into a
procedure. Your procedure must be created with the "sticky" settings that
are needed and can set the others that are needed within the body of the
procedure. I think that approach will work, but I've not investigated all
of the issues to know for certain. Note - your trigger code does not
support mult-row inserts, making the use of a stored procedure much easier
(and as technically flawed the trigger).

Some other alternatives you might want to consider.
* Some form of replication.
* Some form of asynchronous queueing of updates.