25 Aug BizTalk WCF SQL Adapter times-out with Composite Operation
We have the following stored procedure:
XMLNAMESPACES(‘http://XXXYYY.BizTalk.RS.Common.Schemas.CompositeWcfSqlRequest’ as ns0,
And the corresponding composite operation XML schema in BizTalk:
Where the <Any> node is a repeating node (maxOccurs=“unbounded”) corresponding to the following schema:
When we send a “WcfSqlRequest”-message with a large number of repeating “USP_BTS_InsertStagingA701File” nodes (> 100) we get the following error:
How do we solve this problem?
We could increase the MaxConnectionPoolSize to a large number, but we might not know upfront how many operations there will be in our composite operation.
A more elegant solution would be, instead of returning a result set we could use an output parameter to return our results. In our solution the stored procedure looks like this:
When you use the WCF-SQL adapter in combination with a composite operation that returns a result set the you should use an OUTPUT parameter to return the result set instead of using a SELECT statement. Otherwise you could risk to run out of connections.