none
WCF-Custom adapter polling SQL and losing messages RRS feed

  • Question

  • We're using BizTalk Server 2009 with the Adapter Pack. As I understand it, with the adapter pack installed, the WCF adapter basically replaces the older SQL adapter. We run the Add Generated Items... wizard to create schemas, etc. for a Receive port/location to poll a SQL Server database. The resultant resultant location ends up configured to use WCF-Custom. (I'm not sure why it's WCF-Custom and not WCF-SQL, but that's not really why I'm posting. Just curious.)

    The Receive location configuration includes a pollingStatement with two SQL statements - one to retrieve all records with Status=New and the other to update the Status to Submitted. Basically the pollingStatement looks something like this:

    SELECT Long Field List Here FROM MyTable WHERE Status = 'New'; UPDATE MyTable SET Status = 'Submitted' WHERE Status = 'New'

    We have discovered intermittent cases in which records are updated with Status=Submitted, but no message appears in BizTalk, no events are written to the Event Log. The data appears to be polled, but then nothing happens.

    When the pollingStatement includes two SQL statements, does the adapter treat them as transacted so that the two instances of the "WHERE Status = 'New'" criteria in the SELECT and UPDATE states will always affect the same rows. Or, as it's currently written, is it possible that the SELECT is executed, a new row is added, and then the UPDATE is executed and updates all of the selected rows plus the newly row, which then is marked as Submitted but never polled?
    Thursday, January 28, 2010 3:00 PM

Answers

  • OK, here's what I now understand. Using the SQL binding there is no PostPollStatement, so if you want the polling process to mark records as polled, you can do that with a multi-statement PollingStatement setting. Those SQL statements and the write to the MsgBox database will happen within a transaction as long as you have UseAmbientTransaction set to True.

    In my case, I found that the developer had set UseAmbientTransaction to false because of a consistent System.ObjectDisposedException that caused the polling operation to fail. The documentation is clear that, if your polling statement updates the database, as ours does, then UseAmbientTransaction should be True and setting it to False may result in lost messages, as we are experiencing. The System.ObjectDisposedException, I have since discovered, only occurs in our Dev environment so I have set UseAmbientTransaction to True in our Test environment. So far we have not lost a message since setting UseAmbientTransaction to True.

    We are continuing to debug why the adapter fails with the System.ObjectDisposedException when UseAmbientTransaction is set to True in our Dev environment, but I think we have solved the lost messages problem.
    • Marked as answer by Zoe Hart Thursday, February 4, 2010 1:32 PM
    Thursday, February 4, 2010 1:32 PM

All replies

  • I've done some research and found the following in BizTalk Adapter for SQL Server Help (that comes with the Adapter Pack):

    You can specify any number of SQL statements separated by a semicolon. You can use the polling statement to read or update data in a SQL Server database table. The SQL adapter executes the polling statements inside one transaction. When the adapter is used with BizTalk Server, the same transaction is used to submit messages from SQL Server to the BizTalk message box.

    If that's true, then my SELECT statement, UPDATE statement, and the creation of the message in the BizTalk Message Box should all take place within a single transaction. So how is a record marked as Status=Submitted, but no message appears in the Message Box?

    Thursday, January 28, 2010 3:07 PM
  • OK, I'm just going to keep talking to myself on this thread. I think that the surrounding transaction does not prevent the UPDATE transaction from updating a new record inserted in the millisecond between the SELECT and UPDATE statements. I'm going to try changing my pollingStatement to something more like this:

    UPDATE MyTable SET Status = 'Polling' WHERE Status = 'New'; SELECT Long Field List Here FROM MyTable WHERE Status = 'Polling'; UPDATE MyTable SET Status = 'Submitted' WHERE Status = 'Polling'

    A new record inserted anytime after the first UPDATE statement will have Status = 'New' and be ignored by the subsequent SELECT and UPDATE statements.
    Thursday, January 28, 2010 3:19 PM
  • Hi,

    You should not use your polling statement to update the data you polled. Instead, use the PostPollStatement to specify the update statement.

    Thanks,
    Manas
    Friday, January 29, 2010 9:57 AM
  • I'm looking at the WCF-Custom Transport Properties dialog on the Binding tab for SqlAdapterBindingConfigurationElement. This is where we specify our polling statement. There is no PostPollStatement setting there. I searched the BizTalk Adapter Pack SQL Adapter help for PostPollStatement and it's not anywhere in the document. Could it be a setting for a different adapter?
    Friday, January 29, 2010 1:26 PM
  • Zoe,

    PostPollStatement is available in WCF-custom adapter using OracleDBBinding. As for sqlBinding you are right there is no PostPollStatement, which is also the case for WCF-SQL Adapter. If you want to do a select/update simultaneous I suggest calling a Stored-Procedure using WCF-Custom sqlbinding or WCF-SQL Adapter. Maybe this post can be helpful for you: http://geekswithblogs.net/martinabbott/archive/2009/06/03/biztalk-server-2009-and-wcf-sql-adapter.aspx.

    Regards,

    Steef-Jan Wiggers
    MCTS BizTalk Server
    http://soa-thoughts.blogspot.com/
    BizTalk Server
    • Marked as answer by Andrew_ZhuModerator Thursday, February 4, 2010 9:05 AM
    • Unmarked as answer by Zoe Hart Thursday, February 4, 2010 1:25 PM
    Tuesday, February 2, 2010 8:03 AM
    Moderator
  • OK, here's what I now understand. Using the SQL binding there is no PostPollStatement, so if you want the polling process to mark records as polled, you can do that with a multi-statement PollingStatement setting. Those SQL statements and the write to the MsgBox database will happen within a transaction as long as you have UseAmbientTransaction set to True.

    In my case, I found that the developer had set UseAmbientTransaction to false because of a consistent System.ObjectDisposedException that caused the polling operation to fail. The documentation is clear that, if your polling statement updates the database, as ours does, then UseAmbientTransaction should be True and setting it to False may result in lost messages, as we are experiencing. The System.ObjectDisposedException, I have since discovered, only occurs in our Dev environment so I have set UseAmbientTransaction to True in our Test environment. So far we have not lost a message since setting UseAmbientTransaction to True.

    We are continuing to debug why the adapter fails with the System.ObjectDisposedException when UseAmbientTransaction is set to True in our Dev environment, but I think we have solved the lost messages problem.
    • Marked as answer by Zoe Hart Thursday, February 4, 2010 1:32 PM
    Thursday, February 4, 2010 1:32 PM
  • [UPDATE:  2010-10-15]

    This update is long overdue.  Once we implemented all the changes, we were still getting that error from time to time.  The sympton was that we would not get that error for hours, even days, but once we get it, the error keeps coming.  After fiddling with some settings, we found that setting the "PollWhileDataFound" to False did the trick.  We have not seen the error since.

     

    [Original Post]

    We were able to fix our problem by setting the WCF-SQL receive location transaction isolation level.  Go to your BizTalk WCF-SQL receive location --> Click Configure --> Go to Behavior tab --> Set sqlAdapterInboundTransactionBehavior to "ReadCommitted".  We have now set the "UseAmbientTransaction" property to True and have not had any problems for 2 days.

     

    http://geekswithblogs.net/LifeLongTechie/archive/2010/08/25/biztalk-useambienttransaction-and-system.objectdisposedexception.aspx

    • Proposed as answer by Kevin Shyr Thursday, August 26, 2010 2:08 PM
    Thursday, August 26, 2010 2:06 PM