none
How to use transactions in stored procedures with SQL Adapter RRS feed

  • Question

  • Hi,

    I am invoking a stored procedure via the SQL Adapter. Am passing some information, the stored procedure does some deletion and insertion which is wrapped in a transaction. Just want to know if there is anything different that needs to be done when using transactions in the stored procedure invoked by the SQL Adapter?

    Following is the warning in event viewer:

    Details:"HRESULT="0x80004005" Description="Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0."

    HRESULT="0x80004005" Description="Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0."

     

    Following is the structure of my stored procedure:

     BEGIN

      BEGIN TRANSACTION;  
      SET NOCOUNT ON;    
      
       BEGIN TRY    
        -- My DML Statements over here
       END TRY   
       
       BEGIN CATCH
        IF @@TRANCOUNT > 0
         ROLLBACK TRANSACTION;
       END CATCH;
       
       IF @@TRANCOUNT > 0
        COMMIT TRANSACTION;
     END

    Sunday, July 18, 2010 11:15 PM

Answers

All replies

  • I managed to stop this warning from appearing by checking ordered delivery under Transport Advanced Options.

    It was happening in the scenario where there were a large number of messages to process simultaneously.

    But have no explanation why it was throwing the warning in the first place.

    • Edited by TonyVirk Monday, July 19, 2010 4:34 AM clarified explanation
    Monday, July 19, 2010 4:22 AM
  • Hi Tony,

    Information regarding BizTalk SQL Adapter with transaction in SP from MSDN:

    Do not use Transact-SQL transactions in your stored procedure code. SQL stored procedure code that is executed by the SQL adapter is performed under the context of a distributed transaction that is started by the BizTalk messaging engine. If you want to implement error handling logic that will abort the calling transaction, use the Transact-SQL RAISEEROR command in your stored procedure code. Triggering an error with the RAISEERROR command will cause the BizTalk messaging engine to abort the hosting transaction.

    Some useful links related to your issue:

    http://www.eggheadcafe.com/forumarchives/biztalkorchestration/Dec2005/post24745700.asp

    http://geekswithblogs.net/leonidganeline/archive/2006/11/29/99485.aspx

    http://msdn.microsoft.com/en-us/library/aa578440(BTS.20).aspx

    Regards,

    Abhijit


    Please "Mark as Answer" if Post has Answered the Question
    • Marked as answer by TonyVirk Thursday, July 22, 2010 12:07 AM
    Monday, July 19, 2010 8:17 AM
  • Hi Abhijit,

    Thanks, didn't realise that a transaction was already being created

    Regards

    Tony Virk

     

    Thursday, July 22, 2010 12:09 AM