Creating a Trigger which updates a linked server - Heterogeneous queries error

[Barry Schwartz]

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?

[Erland Sommarskog]

Barry Schwartz (ba...@idealsoftware.co.uk) writes:
> 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 ...

I don't think there is anything special with triggers. This sequence of
statements worked for me:

set ansi_nulls off
set ansi_warnings off
go
drop table margit
go
create table margit (a int NOT NULL)
go
set ansi_nulls on
set ansi_warnings on
go
create trigger margit_tri on margit for insert as
set xact_abort on
insert KESÄMETSÄ.tempdb.dbo.margit select * from inserted
go
insert margit values (98)

It appears that the SET ANSI_WARNINGS statement must be active prior
to the INSERT statement. This make sense, at least in the case the
trigger plan is not in cache. When the optmizer tries to build a
plan, the setting is still OFF.

--
Erland Sommarskog, SQL Server MVP, som...@algonet.se
I support PASS - the definitive global community for SQL Server
professionals - http://www.sqlpass.org
The PASS Community Summit is in Seattle Nov 19-22, I'll be there. And you?

[Barry Schwartz]

>.
>

Hi Erland,

I've already tried the way you suggested but I still get
the same error message.

Regards,

Barry.

[Cindy Gross (MS)]

Triggers inherit the properties of the stored procedure or query calling them, not the properties used at the time they are created.

Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

[Erland Sommarskog]

Cindy Gross (MS) (cgr...@online.microsoft.com (Cindy Gross) writes:
> Triggers inherit the properties of the stored procedure or query calling >
them, not the properties used at the time they are created.

No, this is not correct. Consider this script:

set ansi_nulls on
go
create table nisse (a int NOT NULL)
go
set ansi_nulls off
go
create trigger nisse_tri on nisse for insert as
DECLARE @x int
IF @x = NULL
PRINT 'ANSI_NULLS is OFF'
ELSE
PRINT 'ANSI_NULLS is ON'
go
create procedure nisse_sp1 as
INSERT nisse VALUES(12)
go
set ansi_nulls on
go
create procedure nisse_sp2 as
INSERT nisse VALUES(12)
go
EXEC nisse_sp1
EXEC nisse_sp2
go
drop table nisse
drop procedure nisse_sp2
drop procedure nisse_sp1

This prints:

ANSI_NULLS is OFF

(1 row(s) affected)

ANSI_NULLS is OFF

(1 row(s) affected)

Just like for stored procedures, the settings for ANSI_NULLS and
QUOTED_IDENTIFIER are saved with the trigger.

For ANSI_WARNINGS and other, the settings are indeed inherited
from the scope that invoked the trigger.

[Barry Schwartz]

Hi,

I forgot one potentially vital piece of information when
first detailing my problem. The trigger (which updates a
table through a linked server) DOES work if the table is
updated through EM, but NOT when a user updates the
table. Therefore, I guess it's a permissions problem. But
who's (the user's, the linked server connection) and how
do I change it?

Regards

Barry.

[Erland Sommarskog]

No, it is not a permissions issue. Not if you get the message "Heterogeneous
queries require...".

Then the problem is with settings for ANSI_NULLS and ANSI_WARNINGS. Since
the update works if you perform it from Enterprise manager, it appears
that the trigger has ANSI_NULLS on.

Problem is then with the run-time setting for the user for ANSI_WARNINGS.
How does the user connect to the database?

Did you try to connect as the user in Query Analyzer and issue the
UPDATE statement there?