1

I have a linked server and trying to delete something on a table which is in linked server. I am using transaction and try catch in a stored procedure as below.

BEGIN TRY
BEGIN TRANSACTION
DELETE FROM [server].[database].[dbo].[Table]
.
.
.
COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT(ERROR_MESSAGE())
ROLLBACK TRANSACTION
END CATCH

with this code i have many insert and delete in transaction. it is giving this error : OLE DB provider "SQLNCLI11" for linked server "Server" returned message "No transaction is active."

I learnt that if i use SET XACT_ABORT ON it will discard any process if it face any error in runtime it will rollback automatically.

I tried to use SET XACT_ABORT without transaction. it was working but didn't rollback.

Ender Aric
  • 197
  • 4
  • 18

1 Answers1

0

you have rollback Transaction twice in the code. Once it is committed, it will not able to find any active transaction to rollback. Remove 1st Rollback transaction and check.

Ramya
  • 21
  • 5
  • I inserted first rollback wrongly to my pseudo code. My original code doesn't have twice. So my problem is still going on. – Ender Aric May 08 '18 at 23:37