February 1, 2006 at 3:08 pm
I am getting an error message with the trigger below. The query in the trigger checks for a row in the table of a database on a linked server then updates a row in a database on the current server. The error message says that "the transaction was unable to enlist in the specified transaction coordinator".
When I run the query in SQL Query Analyzer (providing values instead of variables) it executes fine. My DBA assures me that the the Distributed Transaction Coordinator service is running on the current server. Can anyone suggest why the trigger may not be working?
Thanks
RB
CREATE TRIGGER [UpdateDA] ON dbo.ImagingWorks
FOR INSERT
AS
DECLARE @IRN char(12)
DECLARE @RequestNo int
SET @IRN = (SELECT IRN FROM inserted)
SET @RequestNo = (SELECT RequestNo FROM inserted)
BEGIN DISTRIBUTED TRANSACTION
IF EXISTS (SELECT * FROM DADJB5BB1S.NGA_IRU.dbo.ProductionControl WHERE IRN = @IRN)
UPDATE ImagingWorks SET DA = 1
WHERE RequestNo = @RequestNo
AND IRN = @IRN
COMMIT TRANSACTION
February 1, 2006 at 3:48 pm
First of all get rid of @IRN and @RequestNo
If eventually you insert 2 or more rows trigger will fail.
You can do it in one statement:
UPDATE ImagingWorks
SET DA = 1
FROM inserted i
INNER JOIN DADJB5BB1S.NGA_IRU.dbo.ProductionControl PC on PC.IRN = i.IRN
WHERE ImagingWorks.RequestNo = i.RequestNo and AND ImagingWorks.IRN = i.IRN
_____________
Code for TallyGenerator
February 1, 2006 at 3:57 pm
I tried your suggestion but I still get the same error message about not being able to enlist in the specified transaction coordinator.
RB
February 1, 2006 at 4:24 pm
Is it what you are looking for?
http://support.microsoft.com/default.aspx?scid=kb;en-us;329332
_____________
Code for TallyGenerator
February 1, 2006 at 4:28 pm
That looks like it, thanks a lot
RB
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply