DB2 Linked Server in a Trigger...

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

Transact-SQL (2000)

DB2 Linked Server in a Trigger...

Author

Topic

Auric
Yak Posting Veteran

70 Posts

Posted - 2006-01-09 : 10:54:17

My ongoing battle with the DB2 Linked server continues,

I now have a working connection, and am able to execute sql statements on the linked server using query analyzer. However, the problem lies in using a trigger on one of my tables.

CREATE TRIGGER Test ON [dbo].[testtable]
FOR UPDATE
AS
set XACT_ABORT on
begin transaction
select * from openquery(Linked,'select * from wwwtest.dsz090')
commit transaction

SQL Statment I try to use in QA
update testtable set comments='Testing' where testid=134

The Error that gets returned.

Server: Msg 7391, Level 16, State 1, Procedure Test, Line 6
The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction.
[OLE/DB provider returned message: [IBM][CLI Driver] SQL1032N No start database manager command was issued. SQLSTATE=57019
]
OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransactionJoin::JoinTransaction returned 0x8004d00a].

If I execute the
select * from openquery(Linked,'select * from wwwtest.dsz090') command in QA it works fine!

Select * from users where clue > 0

Auric
Yak Posting Veteran

70 Posts

Posted - 2006-01-09 : 12:02:54

Update:

If i replicate the same scenario in a Stored Procedure IT WORKS!?

Select * from users where clue > 0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources