BizTalk WCF SQL Adapter times-out with Composite Operation

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



Working at i8c
i8c is a system integrator that strives for an informal atmosphere between its employees, who have an average age of approx 30 years old. We invest a lot of effort in the professional development of each individual, through a direct connection between the consultants and the management (no multiple layers of middle management). We are based in Kontich, near Antwerp, but our customers are mainly located in the triangle Ghent-Antwerp-Brussels and belong to the top 500 companies in Belgium (Securex, Electrabel, UCB, etc…).
Quality Assurance
i8c is committed to delivering quality services and providing customer satisfaction. That’s why we invested in the introduction of a Quality Management System, which resulted in our ISO9001:2000 certification. This guarantees that we will meet your expectations, as a reliable, efficient and mature partner for your SOA & integration projects. i8c also signed the eTIC Benelux charter, which proves our commitment to ethical service delivery.