none
Detect transaction type: distributed or local? RRS feed

  • Question

  • I have some SPs in a "Microsoft SQL Server 2012 (SP3)" database. These SPs might either be called from VB6 applications and other SPs, or from .Net applications.

    If an SP is called from other SPs or VB6 applications I can rollback the current transaction and give proper error messages with raiserror in case of errors/invalid inputs.

    But because connections opened by .Net applications are hooked to distributed transactions if multiple WCF services are involved (it is a company standard, we use MS DTC) I should not roll back the transaction and I should only call raiserror command.

    If we roll back the distributed transactions, all error messages raised with raiserror command are lost and on the .Net side we get an exception whose message is:

    "The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction"

    How can I know if I'm in a distributed transaction or if I'm in a local transaction while coding within SPs? If I'm in a distributed transaction, I will not execute the rollback statement.

    Any help would be appreciated.





    Friday, May 27, 2016 2:00 PM

Answers

  • Severity was 16 already.

    I've found a temp solution for now: I'm checking the view

    sys.dm_pdw_nodes_tran_session_transactions

    The column "is_enlisted" becomes true if I come from .Net where I started a distributed transaction.

    Thursday, June 9, 2016 10:26 AM

All replies

  • Is it the SQL Server SP call MS DTC directly to start a distributed transaction explicitly ? If the client application level Rollback Transaction is restricted why can't we use Database Transaction?

    Regards, RSingh

    Friday, May 27, 2016 2:26 PM
  • No, distributed transaction is opened by WCF application if the transaction involves more than one service. So, we do not open distributed transactions within SPs.

    On the .Net side, we open connections inside TransactionScope sessions and local transaction is escalated to distributed transaction if more than one connection is used in the same transaction scope. 

    On the SP side, my rollback is restricted by some MS DTC behavior which loses all other error messages coming from TSQL code, and gives a generic error message when I execute a rollback statement.

    Friday, May 27, 2016 2:43 PM
  • Can you increase the Severity of RaiseError ?

    For .Net the Severity needs to be greater than 10 in order to trap it.

    Refer:

    https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.infomessage.aspx

    https://msdn.microsoft.com/en-us/library/ms164086.aspx


    Regards, RSingh

    Friday, May 27, 2016 3:37 PM
  • Severity was 16 already.

    I've found a temp solution for now: I'm checking the view

    sys.dm_pdw_nodes_tran_session_transactions

    The column "is_enlisted" becomes true if I come from .Net where I started a distributed transaction.

    Thursday, June 9, 2016 10:26 AM