CODEWITHSUNDEEP
wcfsql-server-2008biztalkbiztalk-2010
Ritley572
Ritley572

Reputation: 309

BizTalk 2010 WCF-SQL adapter XML Polling issue

I have a strange issue which I have not seen before when using the WCF-SQL adapter on a set of receive locations.

The set up is one receive port in BizTalk 2010 with 3 separate receive locations. Each receive location calls the same stored procedure with a different parameter and execute statement looks like this:

EXEC dbo.StoredProc 'WELLNESS'
EXEC dbo.StoredProc 'DIABETES'
EXEC dbo.StoredProc 'SCREENINGS'

The stored proc works exactly as expected when passing the parameters and returns XML using FOR XML PATH. Everything here works fine.

The issue I am running into seems to be in regards to the pollDataAvailableStatement in these receive locations. No matter what I do all 3 return a "1" to BizTalk which kicks off all 3 processes. The polling statements are as follows:

SELECT Count(1) FROM XXX.XXXXTable WHERE File_Type = 'TP - GAPS IN CARE DIABETES' AND File_Status = 'READY'
SELECT Count(1) FROM XXX.XXXXTable WHERE File_Type = 'TP - GAPS IN CARE WELLNESS' AND File_Status = 'READY'
SELECT Count(1) FROM XXX.XXXXTable WHERE File_Type = 'TP - GAPS IN CARE SCREENINGS' AND File_Status = 'READY'

So when I set the flags appropriately in the table it's polling to see if it should run and returning true to execute the stored proc. Even if I only set the flag for the WELLNESS receive location in the database the polling statement returns true for all 3 and I am baffled as to why. I've never run into this before and I am using the same exact setup in many other BizTalk applications with no issue.

Other pieces of information, I am using XmlPolling. I've tried to set the pollWhileDataFound to both True and False and notice the same behavior. The polling interval is 600 seconds (I've also tried 3600 seconds). And all the SQL timeouts are 40 minutes.

Upvotes: 1

Views: 1194

Answers (1)

Ritley572
Ritley572

Reputation: 309

So apparently this is happening because I have the useAmbientTransaction flag set to false in the receive locations. The reason I had set the flag to false was because I was getting database cannot be reached errors when it was set to true, and/or message is closed errors. This happens from time to time because that setting seems buggy.

In short, Ambient Transactions was causing errors so I turned it off. This caused the pollDataAvailableStatement to get skipped and the EXEC statements to run at every polling interval. The stored proc I am calling has a parameter so I changed the exec statement to use the parameter name as well as the value as so:

EXEC dbo.StoredProc @FeedType='DIABETES'

This fixed the issue with Ambient Transaction errors, I was able to set that flag back to true, and now the pollDataAvailableStatement is working as intended. Hopefully this helps someone else.

Upvotes: 2

Related Questions