Connection pooling issue in Reporting Service

Question

Hi,

I'm running SQL Server 2008 R2 with reporting service. I have multiples reports that run on it sharing the same connection via a "Shared datasource". Connection pooling is also active.

If for some reason I need to put MyUserDB (used by the report) in maintenance (setting it to Restricted User temporary), when the DB come back availlable, the first time I try to access the report results in this error:

"Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset 'PrintJob'. ---> System.InvalidOperationException: ExecuteReader requires an open and available Connection. The connection's current state is closed."

The reused connection that was in the pool is no longer availlable due to the service interruption (obviously killed). The second time I try to run the report everything works fine.

Work around: Turning off connection pooling (pooling = false in the connection string) resolve this issue but slow down the report.

Question : Why connection pooling is not more robust? I expect that if a connection is invalidate, drop it from the pool and try the next one. If all connections are invalidate, try creating a new one in a last resort, if still not able to return a valid connection, raising an error will then be the solution.

Is there a way to force a number of retry in the connection attempt?

thanks for your answer

Answer 1

Hi,

Did you try to increase Connect Timeout in Initialization section?

It seams you are getting the error message because of a timeout issue.

PLease share the results.

Cheers. 

---

Please Mark as Answer or Vote As Helpful if a post solves your problem. MCP, MCTS, MCITP

Answer 2

Hi Soheil,

Thanks for your suggestion, my feeling is that it is related to the status of the connection in the pool, the error message doesn't suggest that a new connection is created at this time ("current state is closed"). Unfortunatly, adding Connect Timeout=120 to the connection string has no effect as expected.

My first idea was to force a number of retry in the connection string.

Someone know if it's possible?

thanks

Answer 3

Hi SteveGouin,

Based on my research, the issue may be caused by:

• The code in the application was incorrectly passing a connection object back even if the connection failed to open.
• The underlying error might be a connection timeout to your SQL Server Database.

Here, I suggest that you change the application code to correctly handle the exceptions generated by connections that failed to open, and then set the connect timeout to be a larger value.
If the issue persists, you can refer to the following threads:
http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/32ce6136-006f-40b4-9853-4906ff16232e/
http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/9958b8d0-3e07-49bb-a026-8b40727d8977/

Hope this helps.

Regards,

---

Mike Yin
TechNet Community Support

Answer 4

Hi Mike,

Thanks for your suggestion, unfortunatly I don't have any control over the code because my report are running directly in SSRS. The report has been design with Report Designer and I don't think I can write CATCH exception in that environment (maybe I'm wrong) . Increasing the timeout in the connection string has already been tried without any success.

My feeling is that connection in the connection pool become invalid after been killed (for whatever reason). The question is why SSRS keep a dead connection instead of flushing it from the pool?