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?

 

[gmmastros]

I'm curious to know...

In the stored procedure you are running, do you have a line in there that sets NOCOUNT off?

Ex:

Create Procedure dbo.Blah @Param1 Int
AS
SET NOCOUNT OFF

Select .....

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[skaestner]

Yes I do. Could that possibly be the hang up?

 

[skaestner]

correction it is set to ON instead of OFF.

 

[gmmastros]

Nevermind me. It should be set to ON. Sorry for the confusion. It's best practice to have NOCOUNT set to ON.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom