Linkserver update not working in Transaction

Question

I am trying to do operation on my linked server. I have created one linked server and everything is ok. I just observed one thing which I am not able to understand.

if I run:

update SERVER.testdb.[AAA].MyTable
set MyName = '[email protected]'
where ID = 999

This works fine. But when I execute the same code in following way:

 BEGIN TRY
    BEGIN TRANSACTION TRF

    update SERVER.testdb.[AAA].MyTable
    set MyName = '[email protected]'
    where ID = 999

END TRY
BEGIN CATCH     
    ROLLBACK TRANSACTION TRF    
END CATCH

It says that the parameter is incorrect.

Can anyone tell me why this is happening and what is solution ?

Answer 1

I have tried this on SQL Server 2012. If I run your query as is it completes successfully but does not update anything. But if I do SET xact_abort ON it completes successfully. You can read more about xact_abort on https://msdn.microsoft.com/en-us/library/ms188792.aspx.

SET xact_abort ON 

BEGIN try 
    BEGIN TRANSACTION trf 

    update [SERVER].[testdb].[AAA].[MyTable]
    set MyName= '[email protected]'
    where ID=999 

    --COMMIT TRAN trf 
END try 

BEGIN catch 
    ROLLBACK TRANSACTION trf 
END catch 

What is the SQL Server version you are trying this on? Also, tell us on which line you are getting incorrect parameter error?