July 3, 2009 at 12:28 pm
Hi guys i have a simple trigger here run in a linked server and it result to an error or "The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.
"
Please help
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER TRIGGER [trg_SynctblICMatrixInventoryType]
ON [dbo].[tblICMatrixInventoryType]
--WITH ENCRYPTION
FOR INSERT, UPDATE
AS
BEGIN
UPDATE [ACDB].[LinkedAble703].dbo.ac_Options
SET
Name = I.strType
FROM
inserted I
WHERE I.cntID = intVCCntID
declare @cntID int
declare @strType nvarchar(100)
DECLARE c CURSOR
FOR SELECT cntID, I.strType
FROM inserted I
LEFT OUTER JOIN [ACDB].[LinkedAble703].dbo.ac_Options AS U
ON I.cntID = U.intVCCntID
WHERE U.Name IS null
ORDER BY I.cntID asC
--
OPEN c
FETCH NEXT FROM c INTO @cntID, @strType
--
WHILE (@@FETCH_STATUS=0) BEGIN
INSERT INTO [ACDB].[LinkedAble703].[dbo].ac_Options
([intVCCntID]
,[Name]
,[ShowThumbnails]
,[ThumbnailColumns]
,[ThumbnailWidth]
,[ThumbnailHeight]
,[CreatedDate])
VALUES (
@cntID
, @strType
, 0
, 0
, 0
, 0
, getdate())
FETCH NEXT FROM c INTO @cntID, @strType
END
CLOSE c
DEALLOCATE c
--
UPDATE [ACDB].[LinkedAble703].dbo.ac_ProductOptions
SET
OrderBy = I.intSort
, ProductID = (SELECT TOP 1 P.ProductID FROM [ACDB].[LinkedAble703].dbo.ac_Products P INNER JOIN tblICInventory IC ON P.intVCCntID = IC.cntID WHERE I.strProductID = IC.strProductID)
, OptionID = (SELECT TOP 1 P.OptionID FROM [ACDB].[LinkedAble703].dbo.ac_Options P WHERE P.intVCCntID = I.cntID)
FROM
inserted I
WHERE I.cntID = intVCCntID
INSERT INTO [ACDB].[LinkedAble703].[dbo].ac_ProductOptions
([intVCCntID]
,[ProductID]
,[OptionID]
,[OrderBy])
SELECT
cntID
,(SELECT TOP 1 P.ProductID FROM [ACDB].[LinkedAble703].dbo.ac_Products P INNER JOIN tblICInventory IC ON P.intVCCntID = IC.cntID WHERE I.strProductID = IC.strProductID)
,(SELECT TOP 1 P.OptionID FROM [ACDB].[LinkedAble703].dbo.ac_Options P WHERE P.intVCCntID = I.cntID)
,intSort
FROM inserted I
LEFT OUTER JOIN [ACDB].[LinkedAble703].dbo.ac_ProductOptions AS U
ON I.cntID = U.intVCCntID
WHERE U.OrderBy IS null
END
July 6, 2009 at 8:18 pm
I think the major problem here is your using a cursor-based routine inside a database trigger to update a table over a linked server connection.
First, look at changing the cursor-based routine to a set-based routine.
Second, look for an alternative solution. If the trigger fails, the action that fired the trigger will also abort. Is this acceptable for your application? One alternative I would look at, particularly if the other server is also running SQL Server 2005, is to use Service Broker to transfer that data to the other server and complete the update.
Can't help you much with the Service Broker aspect, but what I'd start with is reading about Service Broker in Books Online.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply