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,
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
No Comments