About biztalk receive location working process

Question

Hi i have a  receive location with pooling interval 2 min.

in procedure at the end i am updating flag 0 to 1.

if procedure executes with in 2 min ,then flag update to 1 so no issue.

if procedure takes more than 2 min  i want to know another instance is started /it will wait till the first one executes?

Answer 1

Hi Dinesh

Let me direct you to this excellent article by John on managing this-

http://social.technet.microsoft.com/wiki/contents/articles/24803.biztalk-server-sql-patterns-for-polling-and-batch-retrieve.aspx

In short, you have to control/flag records at the beginning of the procedure so that a different invocation of the Stored Procedure from a subsequent Polling action does not interfere with the earlier invocation. You will also find this explained in below thread-

http://stackoverflow.com/questions/2165668/biztalk-receive-port-reading-twice-from-db

---

Thanks Arindam

Answer 2

Hi Dinesh,

First of all can you let us know what you are trying to do in your sproc? 

apart from you can use below approach 

use some flag at the starting of your stored procedure so it can help to determine whether your procedure need to proceed or not.

like if you are selecting some fields from some table then first take the unique values in temp table to set the flag present in the table to 0 to 1 means you already taken those values so if your next instance will start after two minutes then also it will not read the values which is already in use.

Regards

Abhay Giri

Mark this as answered or vote , if this help you.

Answer 3

Hi dineshNad,

Refer the article written by Johns-305,

BizTalk Server: SQL Patterns for Polling and Batch Retrieve

It is a wonderful article and describes how to achieve your  Scenario..

Regards

---

Mandar Dharmadhikari

Answer 4

Hi,

1)

Means if procedure taking time more than 2 min, another instance will start ?

2) if another instance started that is also taking more than 2 min then, another instance will start... this chain continuous and data base is blocking completely.

Answer 5

Hi Dinesh,

If the Polling Data Available Check query reports that there are rows which can be polled,those data will be polled after polling interval by the polling query..but keeping the update of the flag at the beginning you can manage the records in much better ways..

But if the polling data is available, the Recive Location will poll the data ..you can manage the polling interval if needs be

Regards,

---

Mandar Dharmadhikari

Answer 6

The correct way to handle this is to write your procedure in a specific way that handles this as shared in the article and link above.

---

Thanks Arindam

Answer 7

Ok i can handle as in the link. this will only correct the issue to not pick the already picked txn.

As i will pick for every 5 sec.

, means fist instance started  updated the first txn flag and it takes more than 2 min,

then next instance started  before first one completed  and  updated the Second txn flag and it takes more than 2 min

if it continuous..... data base is completely blocking.

what is the solution for this.

Answer 8

Thank team for the hat tip.

I am 96% sure the WCF sqlBinding will not stack polling operations.  Meaning, even if one exceeds the Polling Interval, it will not start another.

What I've never had occasion to notice is whether the timer starts at the beginning of a polling operation or at completion of the previous.

This is pretty easy to test.  You can put a WAITFOR DELAY statement in a test SP and see what happens.

Answer 9

Hi Johns,

Great i am expecting this answer. 

I will test as you said.

Also need to know about sql adapter , is this also work like  WCF or not?

Answer 10

You should be using the WCF SQL Adapter/sqlBinding.

The legacy SQL Adapter has been deprecated for nearly 10 years now.