WCF SQL Adapter using OUTPUT parameters rather than a SELECT statement

Recently working on one of the task faced issue, I came through the direction of Thiago’s article on the WCF SQL adapter; which is a very good example. what happens as shown below (ref);

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.

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.