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 500+ customers across
30+ countries depend on BizTalk360

Learn More
Serverless360
Azure

Manage and monitor serverless
components effortlessly

Learn More
Atomicscope
Business Users

Monitor your Business Activity in iPaaS
or Hybrid integration solutions

Learn More

Back to Top