Thursday, August 25, 2011

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

1 comment:

  1. Hi, nice description about BizTalk WCF SQL Adapter.Thanks for your help..

    -Aparna
    Theosoft

    ReplyDelete