BizTalk WCF SQL Adapter times-out with Composite Operation

Problem 
We have the following stored procedure:

CREATE Procedure USP_BTS_InsertStagingA701File

       @RecordInfo         nvarchar(max),

       @ID_BTSOutputFile uniqueidentifier,

       @MD_InternalReferenceSector varchar(15)

AS

BEGIN

       SET NOCOUNT ON;

       DECLARE @ID_BTSFileBizTalkStatus INT

       SELECT @ID_BTSFileBizTalkStatus = status.ID_BTSFileProcessingStatus

       FROM   dbo.BTSFileProcessingStatus AS status with (nolock)

       WHERE  status.Name = 'Processing'

       DECLARE @ID_BTSFileBizTalkInitialStatus INT

       SELECT @ID_BTSFileBizTalkInitialStatus = status.ID_BTSFileProcessingStatus

       FROM   dbo.BTSFileProcessingStatus AS status with (nolock)

       WHERE  status.Name = 'Initial'

      

INSERT INTO [BizTalkStagingDbReference].[dbo].[BTSStagingOutA701MDetailInfo]

           ([ID_BTSStagingOutA701MDetailInfo]

           ,[RecordInfo]

           ,[ID_BTSOutputFile]

           ,[CreDt]

           ,[CreUser]

           ,[ID_BTSFileProcessingStatus]

           ,[ID_BTSFileReferenceProcessingStatus]

           ,[ID_BTSFileDmfaProcessingStatus]

           ,[InternalReferenceSector])

     VALUES

           (newid()

           ,@RecordInfo

           ,@ID_BTSOutputFile

           ,getdate()

           ,substring(suser_sname(),charindex('\',suser_sname())+1,12)

           ,@ID_BTSFileBizTalkStatus

           ,@ID_BTSFileBizTalkInitialStatus

           ,@ID_BTSFileBizTalkInitialStatus

           ,@MD_InternalReferenceSector);

       WITH 
XMLNAMESPACES('http://XXXYYY.BizTalk.RS.Common.Schemas.CompositeWcfSqlRequest' as ns0,

              'http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo' as ns1)

    SELECT @@ERROR as 'ns1:ReturnValue'

    FOR XML PATH('ns1:USP_BTS_InsertStagingA701FileResponse'), ROOT('ns0:WcfSqlRequestResponse')

END
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:

“The adapter failed to transmit message going to send port "InsertProductSingleFile_WCFSQL" with URL "mssql://.//BTSLOC?". It will be retransmitted after the retry interval specified for this Send Port. Details:"Microsoft.ServiceModel.Channels.Common.InvalidUriException: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached. —> System.InvalidOperationException: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.”

Further investigation revealed that the WCF-SQL adapter created 100 connections (the default maximum for the WCF-Custom binding).

Apparently this is a known limitation of the WCF-SQL adapter in combination with composite operations that return a result set:

http://msdn.microsoft.com/en-US/library/dd788151(v=BTS.10).aspx

If there are “n” number of operations in a composite operation that return a result set then “n+1” number of connections are required for the composite operation to be executed. Therefore, you must ensure that the value specified for the MaxConnectionPoolSize binding property is n+1 or greater

 
Solution
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:

CREATE Procedure USP_BTS_InsertStagingA701File

       @RecordInfo         nvarchar(max),

       @ID_BTSOutputFile uniqueidentifier,

       @MD_InternalReferenceSector varchar(15),

       @Response xml out

AS

BEGIN

       SET NOCOUNT ON;

       DECLARE @ID_BTSFileBizTalkStatus INT

       SELECT @ID_BTSFileBizTalkStatus = status.ID_BTSFileProcessingStatus

       FROM   dbo.BTSFileProcessingStatus AS status with (nolock)

       WHERE  status.Name = 'Processing'

       DECLARE @ID_BTSFileBizTalkInitialStatus INT

       SELECT @ID_BTSFileBizTalkInitialStatus = status.ID_BTSFileProcessingStatus

       FROM   dbo.BTSFileProcessingStatus AS status with (nolock)

       WHERE  status.Name = 'Initial'

      

INSERT INTO [BizTalkStagingDbReference].[dbo].[BTSStagingOutA701MDetailInfo]

           ([ID_BTSStagingOutA701MDetailInfo]

           ,[RecordInfo]

           ,[ID_BTSOutputFile]

           ,[CreDt]

           ,[CreUser]

           ,[ID_BTSFileProcessingStatus]

           ,[ID_BTSFileReferenceProcessingStatus]

           ,[ID_BTSFileDmfaProcessingStatus]

           ,[InternalReferenceSector])

     VALUES

           (newid()

           ,@RecordInfo

           ,@ID_BTSOutputFile

           ,getdate()

           ,substring(suser_sname(),charindex('\',suser_sname())+1,12)

           ,@ID_BTSFileBizTalkStatus

           ,@ID_BTSFileBizTalkInitialStatus

           ,@ID_BTSFileBizTalkInitialStatus

           ,@MD_InternalReferenceSector);

      

       WITH  

XMLNAMESPACES('http://XXXYYY.BizTalk.RS.Common.Schemas.CompositeWcfSqlRequest' as ns0,

              'http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo' as ns1)

       SELECT @Response = (SELECT @@ERROR as 'ns1:ReturnValue'

    FOR XML PATH('ns1:USP_BTS_InsertStagingA701FileResponse'), ROOT('ns0:WcfSqlRequestResponse'))

END


Summary
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.

Author: Christophe