ExecuteReader requires an open and available Connection

[skaestner]

The error message that I am receiving has got me stumped as it is almost humorous. I am using SSRS 2008r2 and have had quite success with it up until now. The error message I am receiving in is:

ExecuteReader requires an open and available Connection. The connection's current state is closed.

This is ironic as the Data Source test connection was successful and in the Query Designer the Stored Procedure when ran works and returns the Data Set. However when I click the button OK to return to the DataSet Properties window the Stored Procedure seems to be running again and returns the above error. I am stumped and have had no luck researching through Google or using the MSDN forums. Has anyone got an answer and solution as to what might be causing this error?

 

[xlbo]

Hmmm - that would normally be an issue with programmatic access to the database - along the lines of failing to open the connection before executing the query

Can you give any indication about what the stored proc is doing - is it working with temp tables or table variables by any chance?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[skaestner]

This particular Stored Procedure is actually an interesting one as it is dealing with a linked server connection. Could that possibly be adhering the connection as the SSRS process is opening the connection to the stored procedure and then waits for the connection to the linked server? But that seems odd as it makes a connection during the Query Designer mode with a returned data set. I am still at loss, but this is something to think about.

 

[xlbo]

executereader is a programmatic reference - unless you are coding ADO in your stored proc that's some internal code from SSRS that is bugging out

I guess the only way to test would be to replicate whatever is on the linked server in the initial server and amend teh stored proc to view that. If the error goes away you know it's a linked server issue...possibly an issue to do with how credentials are passed through though I must say I've done queries to from one server to linked servers in SSRS before and not had any issues

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question