WCF SQL Adapter Composite Operation Timeout

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.

This error occurs when using a WCF SQL adapter composite operation with more operations than maxConnectionPoolSize where each operation returns a record set. This is independent of whether useAmbientTransaction is enabled or not. The result set could be as simple as @@ROWCOUNT.

It seems there may be an IDataReader created for each result set. Each result stream retains an open connection until all the requests have been executed. If there are more requests than there are connections available in the connection pool the process will exhaust all available connections in the pool and subsequently fall over.

To fix the issue, redesign the solution or remove the result set from the equation.

[Update] A colleague of mine reworked his sproc to use output parameters to allow a simple response to be returned and avoided this timeout issue.