none
WCF-SQL - Missing data after successful polling of stored procedure RRS feed

  • Question

  • Hello BizTalk Experts,

    I am going a through a odd scenario, the WCF-SQL adapter executes the stored procedure successfully, however data is not found in MessageBox intermittently. This is happening on several Receive Port where the stored procedure and polling SQL server are different. The host instance for WCF-SQL adapter is running from 4 different servers, basically 4 execution of stored procedure at a time.

    I have checked the availability of data in table at cases like this, the data's were perfectly fine. Enabled SQL traces and nothing suspicious was found. 

    Attached the SQL-Binding properties for your reference.

    

    Regards,

    Dani

    Wednesday, March 16, 2016 1:15 PM

Answers

  • Unfortunately, it's the SQL Statements that we would need to see, the rest doesn't matter so much except I will note that if UseAmbientTransaction is false, the PolledDataAvailableStatement is not executed.

    Also, if you have this Receive Location is running on multiple Host Computers, you must be absolutely sure the Stored Procedure supports concurrent executions really, really well.  Otherwise, you will get unpredictable results.

    In every case of 'weirdness' like this, the issue has been in the SQL code, not BizTalk or the Adapter.

    If you have any doubts about the SP, try running in a single Host Instance for a while to see if the problem reoccurs.

    This Wiki Article describes some useful Patterns when Polling SQL Server, or any database: http://social.technet.microsoft.com/wiki/contents/articles/24803.biztalk-server-sql-patterns-for-polling-and-batch-retrieve.aspx

    Wednesday, March 16, 2016 1:44 PM
    Moderator

All replies