Link Server - Trigger Update Table - Error

[Anddmx]

Hi,

I have linked 2 sql server together so I can update one of the tables and I keep getting this error.

TestinServer ----> Links to ----> DevelopmentServer

ERROR:
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]

Both sql servers have DTC install and enabled.

If I remove trigger from the table everything works just fine and this is really fustrating because I have tried all microsoft KB and nothin works for me.

So I think there is something worng with my trigger.

Trigger:
CREATE TRIGGER UpdateBrandId ON [dbo].[Pnr]
FOR INSERT, UPDATE
AS

IF (UPDATE(RecordLocator))
Begin

SET XACT_ABORT ON

Declare @CST varchar(100)

Declare @Brand varchar(100)

Declare @Route varchar(100)

Declare @Username varchar(50)

Declare @UserID varchar(50)

Declare @Booked varchar(6)

Declare @ResID varchar(50)

Declare @ResFullID varchar(20)

Declare @Res bit

Select @Res = (Select LEN(ReservationID) From Inserted)

Select @ResID = (Select ReservationID From Inserted)

Select @Booked = (Select Status From Inserted)

Set @CST = (Select ClientSubTrack From Inserted)

If @ResID = 'tourico_nullno supplier locator'
Begin
Set @Res = 0
End
If @ResID = 'travelworm_nullno supplier locator'
Begin
Set @Res = 0
End
If @ResID IS NULL
Begin
Set @Res = 0
End


If (@Booked = 'Booked' AND @Res = 1)

BEGIN

Set @Brand = (Select substring(@CST, patindex('%brand!%', @CST)+6, patindex('%route!%', @CST)-10))

Set @Route = (Select substring(@CST, patindex('%route!%', @CST)+6, patindex('%)', substring(@CST, patindex('%)%', @CST)+2, len(@CST)))-7))

Set @ResFullID = (Select substring(@ResID, patindex('%travelworm_%', @ResID) + 11, patindex('%no%', @ResID)-12))

Set @Username = (Select Username From Testin.Reservation.dbo.tblRoute Where Route = @Route)

Set @UserID = (Select UserID From Testin.Reservation.dbo.tblRoute Where Route = @Route)

UPDATE Testin.Reservation.dbo.Reservations SET
BrandID = @Brand,
UserName1 = @Username,
UserID1 = @UserID
WHERE ReservationID = @ResFullID
End

End


If someone keep please take look at my trigger and let me know if you see anything wrong this is only my second trigger I have created.

Thanks

--Chris A

 

[SQLDenis]

have you done

SET XACT_ABORT ON as the first thing in your SQL statements?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[Anddmx]

Hi,

Yes I have moved SET XACT_ABORT ON around but still same error.

 

[onpnt]

The distributed transaction error could mean many things.

Have you tried your statements outside of the trigger to make sure there are no errors?

As Denis said SET XACT_ABORT ON must be the first line

CREATE TRIGGER UpdateBrandId ON [dbo].[Pnr]
FOR INSERT, UPDATE
AS
SET XACT_ABORT ON

IF (UPDATE(RecordLocator))
Begin

[sub]____________ signature below ______________
You may get help by means of code here. Just recall what happens to a rat when he eats the cheese off a rat trap while you're Ctrl+C/Ctrl+V'ing that code as your own[/sub]

 

[Anddmx]

Yes...

I think all just put to database on the same server ...save me this headache.