sql server reporting services: Pass parameter to Stored Procedure and wait to process -- sql server reporting services

Pass parameter to Stored Procedure and wait to process

Shawn
8/27/2006 7:55:02 AM

sql server reporting services:

I have a situation where I need to pass a user-parameter to a stored
procedure, which in turn creates a couple of temp tables. The report itself
is three seperate datasets based up the results in the temp tables.
Unfortunately the temp tables take a few minutes to run.

Is there anything I can do that will force the rendering engine to wait for
the stored procedure to finish before trying to render the temp table

Re: Pass parameter to Stored Procedure and wait to process

Bruce L-C [MVP]
8/29/2006 9:14:37 AM

Couple of issues here. First, this won't work even if it took a nanosecond.
RS will work with only one resultset, you have three.
Here are some quick design rules:
1. Only one resultset. Just have your last statement be the select
statement.
2. Do not drop temp tables. Let it just fall out of scope and let SQL Server
manage them. If you explicitly drop them RS will not work.
3. Some people have said that set nocount on causes problems.
4. If you procedure is going to take a long time, then set the timeout with
the dataset (click on the ...)

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

[quoted text, click to view]