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).aspxIf 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