none
WCF SQL Adapter - BTS 2010 - TypedPolling Issue RRS feed

  • Question

  • Hi All,

    I am having tough time to understand the error with the WCF SQL Adapter .

    I have set up a WCF SQL receive location with the following

    PollingAvailableStatement - Select count(*) from Table where field1 = 1

    Polling statement = EXEC GetData;

    Pollwhiledatafound - true

    UseAmbientTransaction - True

    I am succefully able to receive data from the SQL tables with the SQL DB is in the same server as BizTalk.

    But ,when I am configuring remote SQL server in the receive location , I get the following error

    The adapter "WCF-SQL" raised an error message. Details "System.ObjectDisposedException: Cannot access a disposed object.

    Object name: 'TransactionScope'.

    at System.Transactions.TransactionScope.Complete()

    at System.ServiceModel.Dispatcher.TransactionRpcFacet.ThreadLeave()

    at System.ServiceModel.Dispatcher.TransactionBehavior.ClearCallContext(MessageRpc& rpc)

    at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage7(MessageRpc& rpc)".

    So, i tried setting UseAmbientTransaction - false. But I get flooded with messages in BizTalk , due to the fact that pollingavailablestatement doesnt get executed.

    I have checked the DTC ,looks fine. I have downloaded and installed the Hot fix from MS (KB Article Number(s): 2572999
    ).

    Any thought any one . Thanks

     

    Sullu

     

     


    http://biztalkguide.blogspot.com/ Please mark as answer if this solved the issue.Thanks
    Thursday, October 6, 2011 3:03 PM

All replies

  • Does your stored procedure "GetData" commit any transactions?

     

    Morten la Cour

    Thursday, October 6, 2011 5:54 PM
  • Thanks for the reply.

    The SP initially had BEGIN - COMMIT TRAN block and a simple SELECT followed by an UPDATE statement inside.

    I have also tried removing the BEGIN - COMMIT TRAN statement & I could see no difference.

    Any thought ?

    Thanks

    Sullu

     

     


    http://biztalkguide.blogspot.com/ Please mark as answer if this solved the issue.Thanks
    Friday, October 7, 2011 8:13 AM
  • You either have to set  UseAmbientTransaction to false (not recommended), or remove the begin/commit transaction from your SP. It should be BizTalk and only BizTalk that initialize and commits the transaction, since submitting the message to the messagebox should be part of the transaction.

     

    Morten la Cour


    • Edited by la Cour Friday, October 7, 2011 10:06 AM
    Friday, October 7, 2011 9:54 AM
  • Thanks .

    I have already removed the TRAN statement from SQL .

    Setting UseAmbientTransaction to false is ignoring the "PolledDataAvailableStatement", hence 10000 of messages keep coming into biztalk within seconds resulting in blocking of the DB.

    What frustrating me is that the same settings working with the SQL in the same box as BizTalk , but giving error with the Remote SQL.Strange !.

    Regards

    Sullu

     


    http://biztalkguide.blogspot.com/ Please mark as answer if this solved the issue.Thanks
    Friday, October 7, 2011 10:36 AM
  • Have you tried not having any transactions inside your SP and setting useAmbientTrans to true on the adapter? And yes, if you disable transactions on the adapter you need to set pollwhiledatafound = false. Morten la Cour
    • Proposed as answer by DPS Bali Sunday, October 23, 2011 2:48 PM
    • Unproposed as answer by amsullu Monday, October 31, 2011 10:13 AM
    Saturday, October 15, 2011 6:29 AM
  • Hi

    Thanks .Yes I tried this too ,but no luck :(

    Regards

    Sullu


    http://biztalkguide.blogspot.com/ Please mark as answer if this solved the issue.Thanks
    Monday, October 31, 2011 10:14 AM
  • So with userAmbientTrans set to false you still get the error: "System.ObjectDisposedException: Cannot access a disposed object..Object name: 'TransactionScope'"?

    Can you call the stored procedure manually from within SSMS?

    Morten la Cour


    • Edited by la Cour Monday, October 31, 2011 1:20 PM
    Monday, October 31, 2011 10:21 AM
  • Try setting the Transaction isolation Level  http://msdn.microsoft.com/en-us/library/dd788041(v=bts.10).aspx and set the UseAmbientTransactions to “true”. If this still does not solve the problem, then provide the EXACT values for the following properties? Yes you can obfuscate your DB artefact names.

     

    • PolledDataAvailableStatement
    • PollingAction
    • PollingInterval
    • PollingStatement (If you are calling a stored procedure, then can you please also briefly describe the logic used in the stored procedure)
    • PollWhileDataFound
    • PostPollStatement (If you are calling a stored procedure, then can you please also briefly describe the logic used in the stored procedure)
    • UseAmbientTransactions
    • TransactionIsolationLevel (If you don’t know how to find this property, then check this link http://msdn.microsoft.com/en-us/library/dd787944(v=bts.10).aspx)
    • TransactionTimeout (If you don’t know how to find this property, then check this link http://msdn.microsoft.com/en-us/library/dd787944(v=bts.10).aspx)

     

     

    Regards,

     

    Zia


    Hope this helps. Zia Saeed | Don’t forget to mark the post(s) as “Answered” that answered your question
    Monday, October 31, 2011 2:24 PM
  • I tried setting all these values as mentioned above..

    It is working only when the server is local ..that is if i put a . in the binding.

    If it is a remote SQL server , then i still get the same error.

    Note : everything else remains the same in local and remote SQL (tables,SP and the bindings).

    only with the server name difference, there is this error.

     

     


    http://biztalkguide.blogspot.com/ Please mark as answer if this solved the issue.Thanks
    Monday, November 21, 2011 2:26 PM
  • If MSDTC running on both the BizTalk and the SQL Server?

     

    Morten la Cour

    Monday, November 21, 2011 2:42 PM
  • Yes, MSDTC is enabled and running on both servers.

    Only difference is that both servers are in different domains .

    I have tried many possible combinations of testing around this & it is not working only when the Remote SQL server is part of a different domain, which suggests  either some network or firewall issue.But , the network traffic is open and there is no firewall blocking between the servers .DTC is fully configured and running on both the servers.

     

     


    http://biztalkguide.blogspot.com/ Please mark as answer if this solved the issue.Thanks
    • Edited by amsullu Tuesday, November 22, 2011 3:58 PM
    Monday, November 21, 2011 2:49 PM