I have sql db where there will be records inserted on daily basis. I have to poll all the available records everyday, how can this be achieved. what is the easy way to deve

That's a very broad question so the broad answer is with a Stored Procedure you call at a 24 hr interval.

What you have to do is figure out how to identify the 'new' records.

Here's a Wiki article that has some examples: http://social.technet.microsoft.com/wiki/contents/articles/24803.biztalk-server-sql-patterns-for-polling-and-batch-retreive.aspx

My requirement is not just polling updated/new records, always daily at certain time pick all existing and new records. How can i achieve this easily ?

You mean select all records?

SELECT * FROM MyTable

That's about as simple as you can get.

Yes Select all, but do I need to write sp for this ? Select * from MyTableName ?

For receive location you can set up the daily schedule, when this location is enabled.

Design for extracting new records from SQL usually implemented in this way:

• each record got a bit flag which means New / Consumed
• sp CheckNewRecords checks if there are records with flag = New
• sp GetNewRecords selects new records and sets flag = Consumed for these records.

You provide WCF-SQL port with sp (CheckNewRecords and GetNewRecords) names and other set

Leonid, I need to poll daily records. Exisiting + new records need to be polled. Without sp, is it possible to directly select table and poll records ?

It is possible without sp.

Every record should be polled by BizTalk present in the table. All old + new should always be polled from 1 table. What is the way I can achieve this in BizTalk ?

See the adapter parameters. You can set up the PollingIntervalInSeconds parameter to poll daily, but for consistency I would do this on the Port parameters.

Polling interval is fine, but how should I poll. Can you give me step by step procedure like Add generated Items -) sqlbinding

The receive location polls. You don't have to bother with it. You create a port with receive location, that's it.

Host instance initiates all needed pieces (the adapter instance, etc) and schedules the RL code to poll the RL address.

If poll gets the data back, it publishes data as a message to the MessageBox.

Need to?  No.  But, a Stored Procedure is always the better way to go.

Is there something, policy/person, preventing you from doing that?  Just ask the owner of the database to create an SP that returns all records.  Then use the schema wizard to generate you XmlPolling Schema.

Thanks John, but is SP the only option ? My team would hesitate because db is on seperate server outside our env. So controlling the sp will be a problem. Do you have any more better option after that ?

Yes, a Table Operation.  Then the SQL Statement is a SELECT.

Still, push for a Stored Procedure, then fallback to the Table Operation if necessary.

Those are the only two options.

• Marked as answer by Leonid GanelineMVP, Moderator 15 hours 35 minutes ago

Yes, a Table Operation.  Then the SQL Statement is a SELECT.

Still, push for a Stored Procedure, then fallback to the Table Operation if necessary.

Those are the only two options.

• Marked as answer by Leonid GanelineMVP, Moderator Friday, May 29, 2015 3:28 PM

Yes, a Table Operation.  Then the SQL Statement is a SELECT.

Still, push for a Stored Procedure, then fallback to the Table Operation if necessary.

Those are the only two options.

• Marked as answer by Leonid GanelineMVP, Moderator Friday, May 29, 2015 3:28 PM