how to use "Save Transaction" in distributed Transactions

• Vishal_rinkoo

  SSC-Addicted

  Points: 465

  More actions

  December 22, 2005 at 4:10 am

  #67124

  Dear All

  I getting the following error while I am using SAVE TRANSACTION command in Distributed transaction.

  I have 2 servers and both the servers are linked to each other.MSDTC service is also running on both of them.

  I have started the Transaction with

  BEGIN DISTRIBUTED TRANSACTION command still i am getting error . Please help.

  the error is

  Server: Msg 627, Level 16, State 1, Line 2

  Cannot use SAVE TRANSACTION within a distributed transaction.

   

  The code which i am executing is

  BEGIN DISTRIBUTED TRANSACTION

  SAVE TRAN ABC

  SELECT * FROM LINKEDSERVER.ORERMS_V2.DBO.TRANSACTIONLOG

  COMMIT TRAN

• Mark Harr

  SSCrazy Eights

  Points: 9921

  More actions

  December 23, 2005 at 7:03 am

  #611287

  Just as the error message says, you cannot use Save Transaction with distributed transactions.  From BOL:

  SAVE TRANSACTION is not supported in distributed transactions started either explicitly with BEGIN DISTRIBUTED TRANSACTION or escalated from a local transaction.

  Hope this helps.

  
  
  Mark

• Vishal_rinkoo

  SSC-Addicted

  Points: 465

  More actions

  December 30, 2005 at 10:39 pm

  #612234

  I have started transaction with

  BEGIN DISTRIBUTED TRANSACTON statement but still getting the same error.

   

  ??

• Mark Harr

  SSCrazy Eights

  Points: 9921

  More actions

  January 1, 2006 at 11:45 am

  #612251

  You cannot use SAVE TRANSACTION with a distributed transaction.

  I will take a few guesses here at what you are trying to do. 

  If you are not intending to do a distributed transaction, just do BEGIN TRANSACTION.

  If you are intending to complete and write out a distributed the transaction, do COMMIT TRANSACTION.

  If this does not help, it may be best for you to check your Books Online and read up on how transactions operate.

  
  
  Mark