Issue with dataset from stored procedure

I am having an issue with a dataset in SSRS2005. Basically, this is a
simple dataset from a stored procedure, with two char parameters.
I just picked the stored procedure name and let the BI Studio pick all
the default settings. Then I try to preview the data. The BI studio
locks up. This happens every time.
The stored procedure works fine in SSMS. It returns one simple small
data set. It takes 6 seconds to run in SSMS. So I decided to set the
timeout to 30 seconds in the dataset. It times out and gives me the
expected timeout error message. If I set the timeout to 60 seconds, it
locks up - no timeout error message.
If I choose any other similarly structured stored procedure, no
problems occur. So I tried slightly modifying this procedure, but no
luck.
If I watch the procedure in sql profiler, the procedure is started, but
it doesn't complete until I kill BI studio.
Any ideas?hey, scott
when you say you "try to preview the data", do you mean you execute the
query from the data tab in the Report Designer, or do you actually go
to the Preview tab and run the report?
If you mean the latter, then I suggest you take a look at the report
layout, and make sure you're not creating some kind of bottleneck by
doing heavy grouping or formatting.
If you mean the former, I'd encourage you to take a look at the
properties of you data source (shared or in-report) and make sure you
are giving it the correct connection string, and authentication method
(windows/sql/none) and credentials.
Regards,
Thiago Silva
Scott.Stonehouse@.gmail.com wrote:
> I am having an issue with a dataset in SSRS2005. Basically, this is a
> simple dataset from a stored procedure, with two char parameters.
> I just picked the stored procedure name and let the BI Studio pick all
> the default settings. Then I try to preview the data. The BI studio
> locks up. This happens every time.
> The stored procedure works fine in SSMS. It returns one simple small
> data set. It takes 6 seconds to run in SSMS. So I decided to set the
> timeout to 30 seconds in the dataset. It times out and gives me the
> expected timeout error message. If I set the timeout to 60 seconds, it
> locks up - no timeout error message.
> If I choose any other similarly structured stored procedure, no
> problems occur. So I tried slightly modifying this procedure, but no
> luck.
> If I watch the procedure in sql profiler, the procedure is started, but
> it doesn't complete until I kill BI studio.
> Any ideas?|||Sorry, I mean the former. I am doing the preview from the data tab.
I know there is nothing wrong with the connection strings or anything.
I started commenting out fields in the stored procedure, and there are
three fields in particular that appear to be causing the problem.
Again, these look fine when I run it in SSMS. In each of those fields,
I did a coalesce, so I thought maybe that was related, but if I remove
the coalesce, nothing changes.
Then I tried putting the full stored procedure query into the dataset
as text instead of a stored procedure. Everything works. It was cut &
paste, so I know it's the same.
I would just leave it like this, but I have several reports running
from the same procedure. I don't want to have to edit every report
when it's time to make a change to the query.