none
WCF SQL: System.Transactions.TransactionException RRS feed

  • Question

  • Hi,

    I am using WCF SQL Receive port for polling a SQL server database table which always return PollingDataAvailable true.

    Polled message is passed to a singleton orchestration which queries a view to fetch(Select query) required data from database. The database is located at remote server.

    It worked fine for polling interval of 180 secs. When I changed it to 30 sec, it gave me following error :

     

    The adapter failed to transmit message going to send port 
    "ViewOp_dbo_vw_EmpData_WCF-SQL" with URL "mssql://ABC/SQL2K5DEVenv/testDB?
    InboundId=ID1". It will be retransmitted after the retry interval 
    specified for this Send Port. 
    
    Details:"System.Transactions.TransactionException: The operation is not 
    
    valid for the state of the transaction.
      at System.Transactions.TransactionState.EnlistVolatile
    
    (InternalTransaction tx, IEnlistmentNotification 
    
    enlistmentNotification, EnlistmentOptions enlistmentOptions, 
    
    Transaction atomicTransaction)
      at System.Transactions.Transaction.EnlistVolatile
    
    (IEnlistmentNotification enlistmentNotification, EnlistmentOptions 
    
    enlistmentOptions)
      at Microsoft.BizTalk.Adapter.Wcf.Runtime.TxnBatch2..ctor
    
    (IBTTransportProxy transportProxy, ControlledTermination control, 
    
    Transaction transaction, ManualResetEvent orderedEvent, Boolean 
    
    makeSuccessCall)
      at Microsoft.BizTalk.Adapter.Wcf.Runtime.BizTalkDeleteTxn.Execute
    
    (BizTalkExecutionContext executionContext)".

    I have installed :

     I referred to post : http://social.msdn.microsoft.com/Forums/ar-SA/biztalkr2adapters/thread/9d5ee1df-309e-448b-b467-617778f2207c 

    I am not sure if cause is same.

    Request advise.

     

    Thursday, August 18, 2011 12:32 PM

Answers

  • This blog post might help you . You have to make sure that the polling interval is more than the time taken by each polling process to complete doing its job. When you are doing the select from view operation it is timing out and thats why you see the error. You can set the timeout value to a bit longer so that the select from view operation completes successfully. Hope this helps.

    Cheers,

    Bali


    --------------------------------------
    Mark As Answer or Vote As Helpful if this helps.
    Friday, August 19, 2011 2:57 PM

All replies

  • You can start by removing the retry setting from the polling port and share the error in case you get any.

    Cheers.


    --------------------------------------
    Mark As Answer or Vote As Helpful if this helps.
    Friday, August 19, 2011 4:56 AM
  • Thanks for reply Bali!

    I changed retry count to zero. And got the same exception with :

    A message sent to adapter "WCF-SQL" on send port "ViewOp_dbo_vw_EmpData_WCF-SQL" with URI  "mssql://ABC/SQL2K5DEVenv/testDB?InboundId=ID1".  is suspended.

    Error details: System.Transactions.TransactionException: The operation is not valid for the state of the transaction.

    at System.Transactions.TransactionState.EnlistVolatile(InternalTransaction tx, IEnlistmentNotification enlistmentNotification, EnlistmentOptions enlistmentOptions, Transaction atomicTransaction)

    at System.Transactions.Transaction.EnlistVolatile(IEnlistmentNotification enlistmentNotification, EnlistmentOptions enlistmentOptions)

    at Microsoft.BizTalk.Adapter.Wcf.Runtime.TxnBatch2..ctor(IBTTransportProxy transportProxy, ControlledTermination control, Transaction transaction, ManualResetEvent orderedEvent, Boolean makeSuccessCall)

    at Microsoft.BizTalk.Adapter.Wcf.Runtime.BizTalkDeleteTxn.Execute(BizTalkExecutionContext executionContext)

     

    I have no clues of what is wrong.

    Help required.

    Friday, August 19, 2011 9:37 AM
  • Hi,

    It seems like an MSDTC timeout issue. Can this be possible that the polling task is taking more than 30 seconds. In that case your polling interval should be more than the time taken by one hit to SQL.

    Hope this helps.

    Cheers


    --------------------------------------
    Mark As Answer or Vote As Helpful if this helps.
    Friday, August 19, 2011 10:01 AM
  • I am polling a table with single value. And I tested with message polling separately. It is generating messages in every 30 secs.

    The exception generated not on polling port but on select-from-view operation on another WCF-SQL port.

     

    Friday, August 19, 2011 10:36 AM
  • I have defined my orchestration as Long running and kept its components in long running scope to handle any exceptions.

     

    Friday, August 19, 2011 11:12 AM
  • In that case you might need to increase the MSDTC timeout in machine.config, but be informed that this has a system wide effect. So the transaction in select from view operation is timing out. Try to drill time that operation as how much time it is taking and there are many other ways to increase performance by creating proper indexes in your base system table.

    Cheers

     


    --------------------------------------
    Mark As Answer or Vote As Helpful if this helps.
    Friday, August 19, 2011 11:13 AM
  • But when poll messages are received with the intervals of 60 secs. It works successfully. I am bad at Transactions stuff. Request you to elaborate a little.

    Hey.. I missed to mention, I am using singleton orchestration with correlation on Receive port name(WCF-SQL Polling port).

     

    Friday, August 19, 2011 11:41 AM
  • This blog post might help you . You have to make sure that the polling interval is more than the time taken by each polling process to complete doing its job. When you are doing the select from view operation it is timing out and thats why you see the error. You can set the timeout value to a bit longer so that the select from view operation completes successfully. Hope this helps.

    Cheers,

    Bali


    --------------------------------------
    Mark As Answer or Vote As Helpful if this helps.
    Friday, August 19, 2011 2:57 PM