Can't update linked server with transaction

• hasbi_muharip

  SSC Journeyman

  Points: 88

  More actions

  March 24, 2018 at 5:57 am

  #104534

  I have 2 amazon ec2 server which on same workgroup.
  I set ms dtc both pc. but still failing.

  
USE [mydb]
GO

  SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

  ALTER PROC [dbo].[someProc]

  AS
BEGIN
    DECLARE @traname VARCHAR(50)='my procedure name';    
    BEGIN TRAN @traname
    BEGIN TRY
        TRUNCATE TABLE [dbo].[localtable]

          MERGE [dbo].[localtablename] AS target
            USING (SELECT top 5000 [name] FROM [LINKEDSERVER].[somedb].[dbo].[names] WHERE isChecked=0) AS source
            ON target.[name]=source.[name]
        WHEN NOT MATCHED BY target THEN
                INSERT ([name]) VALUES(source.[name]);
            
        UPDATE t1 SET isChecked=1 FROM [LINKEDSERVER].[somedb].[dbo].[names] t1 INNER JOIN [localdb].dbo.localtablename t2 ON t1.name=t2.name

          COMMIT TRAN @traname
    END TRY
    BEGIN CATCH
        ROLLBACK TRAN @traname
        SELECT ERROR_MESSAGE()
    END CATCH

  END

  (5000 row(s) affected)
  OLE DB provider "SQLNCLI11" for linked server "LINKEDSERVER" returned message "No transaction is active.".

  (0 row(s) affected)
  Msg 0, Level 11, State 0, Line 2
  A severe error occurred on the current command. The results, if any, should be discarded.