|
|
|
date: Wed, 6 Aug 2008 16:04:02 -0700 (PDT),
group: microsoft.public.sqlserver.reportingsvcs
back
Re: How to execute stored procs before populating datasets.
On Aug 7, 9:14 am, "Bruce L-C [MVP]"
wrote:
> Temp tables in stored procedures are fine and fully supported. Now, if you
> want to have more than one dataset reference data in the same temp table
> then no, you can't do that. You would need to have a table with the user and
> then use the UserID global available in RS to query the data (query
> parameters can be mapped to an expression, not just to a report parameter> If you use temp tables here is a handy list on how to write your stored
> procedure to use temp tables in a RS compatible way:
> 1. Click on the refresh fields button (to the right of the ...) if your
> field list is not showing up.
>
> 2. Do not use set nocount on
>
> 3. Do not explicitly drop the temp tables. Let the temp tables just fall out
> of scope. SQL Server will properly dispose of them when they are no longer
> needed. I think people explicitly drop them due to habit. Most likely at one
> time it was the proper thing to do. It is not necessary and if you drop them
> then stored procedures will not work with RS.
>
> 4. Have your last statement be a select
>
> If none of these work then add Set FMTONLY Off (the below is from Simon
> Sabin a SQL Server MVP). Here is his explanation: "The issue with RS is that
> the rowset of the SP is defined by calling the SP with SET FMTONLY ON
> because Temp tables don't get created if you select from the temp table the
> metadata from the rowset can't be returned. This can be worked around by
> turning FMTONLY OFF in the SP."
>
> I have found this to only be an issue when you create a temp table in your
> stored procedure that is then filled with data from another stored
> procedure.
>
> --
>
> Bruce Loehle-Conger
>
> MVP SQL Server Reporting Services
>
> "Neufusion" wrote in message
>
> news:ffb8d287-3bb6-495a-9a1a-3b48a289572a@z11g2000prl.googlegroups.com...
>
>
>
> > Two questions
> > 1. How do you execute code (or stored proc) that populates/updates
> > tables BEFORE the datasets in the report are populated? Additionally,
> > it needs to wait until the code is finished.
> > EX: User opens report, it executes code to load/update tables, WAITS
> > until it is complete, and then populates teh datasets and generates
> > the report.
>
> > 2. If this is possible, is it possible to generate temp tables in the
> > beginning that can be accessed by the datasets?- Hide quoted text -
>
> - Show quoted text -
OK, so I can create a table that will use the UserID so that all
datasets can load the data from that table.
How do I make the report execute some code to populate that table and
let it finish populating before the datasets pull data from it?
date: Thu, 7 Aug 2008 11:21:56 -0700 (PDT)
author: Neufusion
Re: How to execute stored procs before populating datasets.
RS executes datasets in the order they are the RDL (XML file). Create a new
report. Add a dataset and have it make the call. Next add the other datasets
that query the table. You can check the RDL file (right mouse click on the
report, view code) and make sure that the dataset you want is first.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Neufusion" wrote in message
news:27aecc90-2ad0-4828-bdad-2aebdbb02377@i24g2000prf.googlegroups.com...
On Aug 7, 9:14 am, "Bruce L-C [MVP]"
wrote:
> Temp tables in stored procedures are fine and fully supported. Now, if you
> want to have more than one dataset reference data in the same temp table
> then no, you can't do that. You would need to have a table with the user
> and
> then use the UserID global available in RS to query the data (query
> parameters can be mapped to an expression, not just to a report
> parameter).
>
> If you use temp tables here is a handy list on how to write your stored
> procedure to use temp tables in a RS compatible way:
> 1. Click on the refresh fields button (to the right of the ...) if your
> field list is not showing up.
>
> 2. Do not use set nocount on
>
> 3. Do not explicitly drop the temp tables. Let the temp tables just fall
> out
> of scope. SQL Server will properly dispose of them when they are no longer
> needed. I think people explicitly drop them due to habit. Most likely at
> one
> time it was the proper thing to do. It is not necessary and if you drop
> them
> then stored procedures will not work with RS.
>
> 4. Have your last statement be a select
>
> If none of these work then add Set FMTONLY Off (the below is from Simon
> Sabin a SQL Server MVP). Here is his explanation: "The issue with RS is
> that
> the rowset of the SP is defined by calling the SP with SET FMTONLY ON
> because Temp tables don't get created if you select from the temp table
> the
> metadata from the rowset can't be returned. This can be worked around by
> turning FMTONLY OFF in the SP."
>
> I have found this to only be an issue when you create a temp table in your
> stored procedure that is then filled with data from another stored
> procedure.
>
> --
>
> Bruce Loehle-Conger
>
> MVP SQL Server Reporting Services
>
> "Neufusion" wrote in message
>
> news:ffb8d287-3bb6-495a-9a1a-3b48a289572a@z11g2000prl.googlegroups.com...
>
>
>
> > Two questions
> > 1. How do you execute code (or stored proc) that populates/updates
> > tables BEFORE the datasets in the report are populated? Additionally,
> > it needs to wait until the code is finished.
> > EX: User opens report, it executes code to load/update tables, WAITS
> > until it is complete, and then populates teh datasets and generates
> > the report.
>
> > 2. If this is possible, is it possible to generate temp tables in the
> > beginning that can be accessed by the datasets?- Hide quoted text -
>
> - Show quoted text -
OK, so I can create a table that will use the UserID so that all
datasets can load the data from that table.
How do I make the report execute some code to populate that table and
let it finish populating before the datasets pull data from it?
date: Thu, 7 Aug 2008 13:51:40 -0500
author: Bruce L-C [MVP]
|
|
