November 28, 2002 at 4:53 am
When trying to update a table in a linked server (SQL7 on
NT4) from a trigger (in a table on SQL2000 on W2000), I
get the following error message:
"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."
The following work for stored procedures, but not for
triggers:
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO
CREATE TRIGGER CustomerInsert
ON [scheme].[slcustm]
FOR INSERT
AS
SET XACT_ABORT ON
INSERT INTO linkedserver.database.owner.table
SELECT ...
and also:
SET ANSI_NULLS ON
GO
CREATE TRIGGER CustomerInsert
ON [scheme].[slcustm]
FOR INSERT
AS
SET ANSI_WARNINGS ON
SET XACT_ABORT ON
INSERT INTO linkedserver.database.owner.table
SELECT ...
I still get the same error message. Is a different
approach needed for triggers?
BOL for 'Create Trigger' states:
"Any SET statement can be specified inside a trigger. The
SET option chosen remains in effect during the execution
of the trigger and then reverts to its former setting."
That sounded great as I dont want to permanently alter any
settings, so I tried:
CREATE TRIGGER CustomerInsert
ON [scheme].[slcustm]
FOR INSERT
AS
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SET XACT_ABORT ON
INSERT INTO linkedserver.database.owner.table
SELECT ...
Still the same error message though!! What am I missing?
November 28, 2002 at 5:14 am
Your error message is complaining about the connection that is issuing the INSERT into [scheme].[slcustm] table, not the connection that created the Trigger, or the options that the trigger is setting.
It is that client connection that need the ANSI options set before it executes the INSERT statement
November 28, 2002 at 5:50 am
That makes sense. How do I change the settings (I'm a programmer rather than a DBA so apologies for ignorance - please do feel free to teach granma' to suck eggs!)?
November 28, 2002 at 7:06 am
That depends on your client.
If you are running the INSERT statement from Query Analyzer, then before you run the INSERT statement, just run the SET ANSI_NULLS ON and SET ANSI_WARNINGS ON exactly as in your original post.
November 28, 2002 at 8:02 am
Sorry, the client is a linked server connection.
December 21, 2004 at 2:45 pm
I have ran into the same problem also with a trigger. I have done the following in QA:
set ansi_nulls on
set ansi_warnings on
go
create trigger x on table y as
set xact_abort on
go
Unfortunately this does not seem to work with triggers. Any ideas? I cannot believe I am the only one doing this.
MG.-
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply