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;
ALTER PROCEDURE [dbo].[ADD_PRODUCT]
@ProductShortDescription
SET NOCOUNT ON;
–based on product short description
MERGE INTO Product AS Target
USING
AS
ON
WHEN
UPDATE
WHEN
INSERT
VALUES
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;
@ProductShortDescription varchar(50),
@Hello varchar(50) OUTPUT
AS
BEGIN
–based on product short description
MERGE INTO Product AS Target
USING
AS
WHEN
UPDATE
WHEN
INSERT
, UnitPrice)
VALUES
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.
