WCF SQL Adapter times-out with Composite Operation

Posted: April 6, 2011  |  Categories: BizTalk Uncategorized

Recently one of my colleagues came to me with this problem and I pointed him in the direction of Thiago’s article on the WCF SQL adapter. A little while later they came back to tell me that yes that Thiago’s example worked but his one did not work. I now looked at what they were doing a bit more closely. I noticed that they were returning a value from their stored procedure whereas in Thiago’s example he was not. I changed the stored procedure in Thiago’s example to return ‘hello”  to test what happens as shown below;

— =============================================
— Author: Thiago Almeida
— Create date: June 2009
— Description: Sample for BizTalk 2009 Webcasts
— =============================================
ALTER PROCEDURE [dbo].[ADD_PRODUCT]
@ProductShortDescription
varchar(50),
@ProductFullDescription varchar(max),
@UOM nchar(10),
@UnitPrice money
AS
BEGIN
SET NOCOUNT ON;
–Use merge statement to either insert or update product
–based on product short description
MERGE INTO Product AS Target
USING
(SELECT @ProductShortDescription, @ProductFullDescription , @UOM, @UnitPrice)
AS
Source(ProductShortDescription, ProductFullDescription
, UOM, UnitPrice)
ON
(Target.ProductShortDescription = Source.ProductShortDescription)
WHEN
matched THEN
UPDATE
SET ProductFullDescription = Source.ProductFullDescription
, UOM = Source.UOM
, UnitPrice = Source.UnitPrice
WHEN
not matched THEN
INSERT
(ProductShortDescription, ProductFullDescription, UOM
, UnitPrice)
VALUES
(Source.ProductShortDescription, Source.ProductFullDescription
, Source.UOM, Source.UnitPrice);
SELECT ‘HELLO’ AS Hello —Added to send a value back in the response
END

I had to change the SQL schema too, as shown below.

ADD_PRODUCT

Now when I fired up the same example I got the similar error to what my colleague has got namely;

“The adapter failed to transmit message going to send port "InsertProductSingleFile_WCFSQL" with URL "mssql://.//BT09WebcastsInvoice?". 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.”

I executed sp_who in SQL management studio to find out how many connection to the database had been opened by the composite WCF-SQL adaptet. I found the the composite adapter had created 100 connections ( the maximum that was set in the WCF-Custom binding).

Why is the WCF-SQL adapter creating so many connections when a record set is returned? I think the answer is that a separate connection is opened until all the results are returned but unfortunately we run out of connections before the entire composite transaction has finished.

{UPDATE 2011-04-08 Communication from Thiago

“…, it’s a known limitation:

http://msdn.microsoft.com/en-US/library/dd788151(v=BTS.10).aspx

I guess they would tell you to increase the MaxConnectionPoolSize value to a value larger than the number of operations you expect to bunch together, the max value being 2,147,483,647. “  }

Next I changed the stored procedure to return the result using an OUTPUT parameter instead of a SELECT statement like so;

ALTER PROCEDURE [dbo].[ADD_PRODUCT]
@ProductShortDescription
varchar(50),
@ProductFullDescription varchar(max),
@UOM nchar(10),
@UnitPrice money,
@Hello varchar(50) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
–Use merge statement to either insert or update product
–based on product short description
MERGE INTO Product AS Target
USING
(SELECT @ProductShortDescription, @ProductFullDescription , @UOM, @UnitPrice)
AS
Source(ProductShortDescription, ProductFullDescription , UOM, UnitPrice)
ON
(Target.ProductShortDescription = Source.ProductShortDescription)
WHEN
matched THEN
UPDATE
SET ProductFullDescription = Source.ProductFullDescription , UOM = Source.UOM
, UnitPrice = Source.UnitPrice
WHEN
not matched THEN
INSERT
(ProductShortDescription, ProductFullDescription, UOM
, UnitPrice)
VALUES
(Source.ProductShortDescription, Source.ProductFullDescription,UOM
, Source.UOM, Source.UnitPrice);
–SELECT ‘HELLO’ AS Hello —Added to send a value back in the response
SET @Hello = ‘HELLO’
END

Running the sample again, the data is inserted in to the database without any error because only one pooled connection is created.

In summary if you use a WCF-SQL adapter with a composite operation and you want to return a result set then you must do this using OUTPUT parameters rather than a SELECT statement. if you don’t then you risk running out of connections.

BizTalk360
BizTalk Server

Over 650+ customers across
30+ countries depend on BizTalk360

Learn More
Serverless360
Azure

Operate efficiently with enterprise-grade Azure monitoring,
tracing, remediation & governance in one platform

Learn More

Back to Top