none
Polling with WCF SQL Adapter not working RRS feed

  • Question

  • Hi Guys,

    I have a receive location setup up with the PolledDataAvailableStatement set as this query:

    "SELECT COUNT(*) AS Expr1 FROM MyTable WHERE  (Id = '001') AND (Extracted IS NULL)"

    and the polling statement set as:

    "SELECT  *  FROM  MyTable WHERE  (Id = '001') AND (Extracted IS NULL);
    UPDATE MyTable SET Extracted = 'T', ExtractTime = GETDATE() WHERE (Id = '001') AND (Extracted IS NULL)"

    based on an article I read which says the update statement will execute after select to update those records, as soon as I start the location I get this error every minute (my polling period) in Event viewer:

    Event Type: Warning
    Event Source: BizTalk Server 2009
    Event Category: BizTalk Server 2009
    Event ID: 5740
    Date:  5/9/2010
    Time:  12:42:39 PM
    User:  N/A
    Computer: 
    Description:
    The adapter "WCF-Custom" 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)
       at System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet)".

    What is the problem? I don't see any suspended messages so does this mean the orchestraction wasn't even invoked?

    Sunday, May 9, 2010 9:54 AM

Answers

  • Hi,

    Regarding the error you look at this thread, which suggest to set UseAmbientTransaction property to False. Look at this thread on the forum concerning the information messages in event viewer.

    HTH

    Regards,

    Steef-Jan Wiggers
    MCTS BizTalk Server
    http://soa-thoughts.blogspot.com/
    If this answers your question please mark it accordingly


    BizTalk
    • Marked as answer by Samual Downey Tuesday, May 11, 2010 5:55 AM
    Monday, May 10, 2010 12:45 PM
    Moderator
  • I think it depends on whether the table you are interacting with is local or distributed (like a linked server) or if DTC is involved at any level. You may not want to set UseAmbientTransaction to false. I know this approach usually works (I have used this solution before too), but it does not really explain the root problem.

    I wonder if you removed the semicolon or restructured the poll statement at all if this would solve the problem. Perhaps the adapter opens a transaction and loops on each block of the batch, perhaps committing on each one. This would cause the error by trying to commit on an already closed transaction. It is like the adapter assumes there will be only one block in the batch and perhaps no "Go" or ";" syntax to separate statement blocks of the batch.

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    • Marked as answer by Samual Downey Tuesday, May 11, 2010 6:42 AM
    Tuesday, May 11, 2010 6:07 AM
    Moderator

All replies

  • Hi Samual,

    I haven't seen this behaviour before and there are some thread displaying the same behaviour like this, but it does not explain the behaviour. Are you working with Adapter Pack? And are you polling Oracle? What article did you read? I am just curious to know more about your scenario.

    Regards,

    Steef-Jan Wiggers
    MCTS BizTalk Server
    http://soa-thoughts.blogspot.com/
    If this answers your question please mark it accordingly


    BizTalk
    Sunday, May 9, 2010 1:15 PM
    Moderator
  • Hi Steef,

    No I'm polling Sql Server database. Yes I'm working with the adapter pack. This is the article I used as a reference to start with  (polling data)

    http://www.packtpub.com/article/soa-capabilities-in-bizTalk-wcf

    Monday, May 10, 2010 6:00 AM
  • Just a note, not sure if this is related but I don't remember seeing this in other environments before, I am seeing ALOT of information messages in Event viewer saying


    "Communication with MessageBox BizTalkMsgBoxDb on SQL Instance RRRRRR has been re-established"

    and

    "Communication with all MessageBoxes has now been re-established"

     

    Is this normal? could this be relatedf to the problem I'm facing? Although I am connecting to another SQL server database not on the local server for polling

    Monday, May 10, 2010 6:55 AM
  • Hi,

    Regarding the error you look at this thread, which suggest to set UseAmbientTransaction property to False. Look at this thread on the forum concerning the information messages in event viewer.

    HTH

    Regards,

    Steef-Jan Wiggers
    MCTS BizTalk Server
    http://soa-thoughts.blogspot.com/
    If this answers your question please mark it accordingly


    BizTalk
    • Marked as answer by Samual Downey Tuesday, May 11, 2010 5:55 AM
    Monday, May 10, 2010 12:45 PM
    Moderator
  • The Communication with MessageBox messages are common and normal - I see this all the time on all of my BizTalk environments. I think this is like a network heartbeat.

    Richard's screenshot in the article shows UseAmbientTransaction=true. I would make sure your other properties match the screenshot.

    When I use polling I do not update the table I am polling against in the polling statement. The ObjectDisposedException probably means that the TransactionScope used previously to execute the poll has already occurred. Perhaps you could move the update to an orchestration or database call after receiving the poll notification.

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Monday, May 10, 2010 5:16 PM
    Moderator
  • Steef,

     

    You were right, changing the UseAmbientTransaction to false fixed the problem although as Ben says (Richard's screenshot in the article shows UseAmbientTransaction=true)

    so can you explain why this is?

    Tuesday, May 11, 2010 5:54 AM
  • I think it depends on whether the table you are interacting with is local or distributed (like a linked server) or if DTC is involved at any level. You may not want to set UseAmbientTransaction to false. I know this approach usually works (I have used this solution before too), but it does not really explain the root problem.

    I wonder if you removed the semicolon or restructured the poll statement at all if this would solve the problem. Perhaps the adapter opens a transaction and loops on each block of the batch, perhaps committing on each one. This would cause the error by trying to commit on an already closed transaction. It is like the adapter assumes there will be only one block in the batch and perhaps no "Go" or ";" syntax to separate statement blocks of the batch.

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    • Marked as answer by Samual Downey Tuesday, May 11, 2010 6:42 AM
    Tuesday, May 11, 2010 6:07 AM
    Moderator
  • Thanks Ben, I will keep that in mind
    Tuesday, May 11, 2010 6:42 AM
  • Just a note, not sure if this is related but I don't remember seeing this in other environments before, I am seeing ALOT of information messages in Event viewer saying


    "Communication with MessageBox BizTalkMsgBoxDb on SQL Instance RRRRRR has been re-established"

    and

    "Communication with all MessageBoxes has now been re-established"

     

    There is a patch for this BizTalk 2009 issue to squelch those log entries.
    Monday, July 26, 2010 6:21 PM
  • I received this message because I accidentally misspelled InboundId connection property with "InboundID" and UseAmbientTransaction was set to True.  When I changed it to "InboundId" (correct spelling), it worked fine.

    Sean Boman

    Tuesday, January 25, 2011 10:38 PM
  • The issue with setting useAmbientTransaction=False in a TypedPolling interface is that the DataAvailable statement then does not fire.  Instead, the WCF just goes after the data every time the interval comes up.

    Does anyone know how to set useAmbientTransaction=false, and still have the polledDataAvailable statement trigger the pollingStatement?

    This is an issue in BizTalk 2010 as well.


    mp

    Friday, September 7, 2012 7:34 PM