Heterogeneous trigger error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Heterogeneous trigger error

Author

Topic

ctwilliams
Starting Member

1 Post

Posted - 2002-07-17 : 18:03:57

I have created a "for update" trigger on a table on Server1 that updates a table on Server2. I set ANSI_NULLS and ANSI_WARNINGS to "ON" before creating my trigger in SQL Query Analyzer.

The query works great when I update the table on Server1 from Query Analyzer -- the trigger fires and the table on Server2 is correctly updated. However, when I update the table on Server1 from within a client application, the trigger fires but I get the following error:

"Heterogeneous queries require 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."

I have read other posts on this board telling me to create the trigger in Query Analyzer and to set ANSI_NULLS and ANSI_WARNINGS to "ON" -- I have done both of these. Does anyone have any other suggestions?

Here is my trigger code:

SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE TRIGGER tr_UpdateServer2
ON Table1
FOR UPDATE AS

IF UPDATE MyField)
BEGIN
DECLARE @ID varchar(15)
DECLARE @MyField varchar(15)

SELECT @ID=ID , @MyField=MyField FROM inserted

Set XACT_ABORT ON -- allow nested transaction

UPDATE [Server2].Database2.dbo.Table2
SET MyField = @MyField
WHERE ID = @ID
END
GO

nr
SQLTeam MVY

12543 Posts

Posted - 2002-07-17 : 18:47:13

The trigger works ok when you update from query analyser so there's nothing inherently wrong with it. It must be something to do with the context in which the client is working.

Try running the profiler and check what is set when youre client connects and when it fires the trigger. You'll probably find that it has changed these settings.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources