none
Real purpose of the "PolledDataAvailableStatement" property in the WCF-SQL Adapter BizTalk 2010 RRS feed

  • Question

  • I want to know the real purpose of the "PolledDataAvailableStatement" property in the WCF-SQL Adapter. I have created an adapter and keep it empty. Then it gives me an error as

    The Messaging Engine failed to add a receive

    location "WcfTestReceiveLocation" with

    URL "mssql://.//AdapterTest?/erer" to the adapter

    "WCFSQLAdapter". Reason: "Microsoft.ServiceModel.Channels.Common.AdapterException:

    The value "" for the property PolledDataAvailableStatement is invalid.

    What I really want to know is is this property mandatory? How this property behave when the data polling from the database.

    Note: I used a procedure to return the data from database and it returns a xml. I used the polling type as "XmlPolling" in the adapter

    Thursday, July 19, 2012 5:31 AM

Answers

  • Just want to know what makes harm if you keep default(Select 1 from Dual). ANy specific reason to delete?

    WHat is the value for property PollWHileDataFound property?

    Suppose, Polling interval is 600(consider Pollwhiledatafound is false), BizTalk adapter will execute query specified in PollDataavailablestatement for every 10 mins, if any row is returned, then Poll statement and Post poll statement is executed in the transaction specified(serializable,read commited,etc). If transaction is completed in 2 mins, then rest of the time BizTalk adapter sleeps.

    If you want to execute your procedure on timely basis then keep it default, if you want to check if records exist before executing procedure, then specify custom defined statement.

    Otherway(Pollwhiledatafound is true), BizTalk will continuously poll the database and varifies any record is returned by PollDataavailablestatement, if yes then proceeds with Poll statement. If not then it wont wait for the next polling interval to open a connection. So there might be chances that polling can be executed twice with in polling interval.

    To answer your question, yes, it is mandatory to have polleddataavailablestatement.

    Thursday, July 19, 2012 6:24 AM
  • PolledDataAvailableStatement is executed every interval you set in pollingIntervalInSeconds. Then if it returns a result

    it will execute the sql statement you define in pollingStatement.

    In the PolledDataAvailableStatement you may want to optimize it and return single row as possible. Like for instance the table is being filled with lots of records.

    : in the PolledDataAvailableStatement you'll specify like:

    SELECT TOP 1 * FROM YourTable WHERE Status = 0;

    : In pollingStatement:

    EXEC [YourStoredProcedureThatReturnsAllStatusWithZero]

    Also, if MSDTC is turned off you can set the UserAmbientTransaction = False but in your stored procedure after

    the select statement, you need to add the update statement that changes the status to different value.


    Randy Aldrich Paulo

    MCTS(BizTalk 2010/2006,WCF NET4.0), MCPD | My Blog


    BizTalk Message Archiving - SQL and File
    Automating/Silent Installation of BizTalk Deployment Framework using Powershell >
    Sending IDOCs using SSIS

    Thursday, July 19, 2012 7:52 AM

All replies

  • Just want to know what makes harm if you keep default(Select 1 from Dual). ANy specific reason to delete?

    WHat is the value for property PollWHileDataFound property?

    Suppose, Polling interval is 600(consider Pollwhiledatafound is false), BizTalk adapter will execute query specified in PollDataavailablestatement for every 10 mins, if any row is returned, then Poll statement and Post poll statement is executed in the transaction specified(serializable,read commited,etc). If transaction is completed in 2 mins, then rest of the time BizTalk adapter sleeps.

    If you want to execute your procedure on timely basis then keep it default, if you want to check if records exist before executing procedure, then specify custom defined statement.

    Otherway(Pollwhiledatafound is true), BizTalk will continuously poll the database and varifies any record is returned by PollDataavailablestatement, if yes then proceeds with Poll statement. If not then it wont wait for the next polling interval to open a connection. So there might be chances that polling can be executed twice with in polling interval.

    To answer your question, yes, it is mandatory to have polleddataavailablestatement.

    Thursday, July 19, 2012 6:24 AM
  • Thanks for the reply...
    Thursday, July 19, 2012 7:08 AM
  • PolledDataAvailableStatement is executed every interval you set in pollingIntervalInSeconds. Then if it returns a result

    it will execute the sql statement you define in pollingStatement.

    In the PolledDataAvailableStatement you may want to optimize it and return single row as possible. Like for instance the table is being filled with lots of records.

    : in the PolledDataAvailableStatement you'll specify like:

    SELECT TOP 1 * FROM YourTable WHERE Status = 0;

    : In pollingStatement:

    EXEC [YourStoredProcedureThatReturnsAllStatusWithZero]

    Also, if MSDTC is turned off you can set the UserAmbientTransaction = False but in your stored procedure after

    the select statement, you need to add the update statement that changes the status to different value.


    Randy Aldrich Paulo

    MCTS(BizTalk 2010/2006,WCF NET4.0), MCPD | My Blog


    BizTalk Message Archiving - SQL and File
    Automating/Silent Installation of BizTalk Deployment Framework using Powershell >
    Sending IDOCs using SSIS

    Thursday, July 19, 2012 7:52 AM
  • "PolledDataAvailableStatement" property

    Specifies the SQL statement executed to determine whether any data is available for polling for a specific table in a SQL Server database.The specified statement must return a result set consisting of rows and columns. The value in the first cell of the result set indicates whether the adapter executes the SQL statement specified for the PollingStatement binding property. If the first cell of the result contains a positive value, the adapter executed the polling statement.

    Following are examples of some valid statements that you can specify for this
    binding property:

    - If you are specifying a SELECT statement:

    SELECT COUNT(*) from <table_name>

    - If you are specifying a stored procedure, your stored procedure might be defined as:

    CREATE PROCEDURE <procedure_name> AS BEGIN SELECT COUNT(*) FROM <table_name> END GO

    or

    CREATE PROCEDURE <procedure_name> AS BEGIN DECLARE @count int SELECT @count = SELECT(*) FROM <table_name> SELECT @count END GO

    If you are using a stored procedure, you would specify the PolledDataAvailableStatement as EXEC <procedure_name>.


    Nagaraja Karur

    Thursday, December 28, 2017 5:24 AM