25 Aug 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).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
