| Groups | Blog | Home |
sql server reporting services : Using temp tables in RS
Hello,
I am using a stored procedure that's using table variables and a temp table for querying purposes. I want to create a report in Reporting Services using this stored procedure as a datasource but it will not allow temp tables. I am currently using SQL2000. Please help. Thanks, Allison
What error are you getting?
-- Ravi Mumulla (Microsoft) SQL Server Reporting Services This posting is provided "AS IS" with no warranties, and confers no rights. [quoted text, click to view] "Ha Vo" <ha.vo@prometheuslabs.com> wrote in message news:ugrd66pbEHA.3716@TK2MSFTNGP11.phx.gbl... > Hello, > > I am using a stored procedure that's using table variables and a temp table > for querying purposes. I want to create a report in Reporting Services using > this stored procedure as a datasource but it will not allow temp tables. I > am currently using SQL2000. Please help. > > Thanks, > Allison > >
Try re-writing your stored procedure to use table variables only and not use temp tables or create table statements.
[quoted text, click to view] "Ha Vo" wrote:
> Hello, > > I am using a stored procedure that's using table variables and a temp table > for querying purposes. I want to create a report in Reporting Services using > this stored procedure as a datasource but it will not allow temp tables. I > am currently using SQL2000. Please help. > > Thanks, > Allison > >
I am using table variables with all the tables in my query except one table
( person table). This person table is a huge table...hence when I use all table variables, the query takes 9 minutes plus to return data. Please help. Thanks. Allison. "B. Mark McKinney" <BMarkMcKinney@discussions.microsoft.com> wrote in message news:17D7CE0D-2BD1-45E8-B80E-8AC7A696E9A9@microsoft.com... [quoted text, click to view] > Try re-writing your stored procedure to use table variables only and not use temp tables or create table statements. > > "Ha Vo" wrote: > > > Hello, > > > > I am using a stored procedure that's using table variables and a temp table > > for querying purposes. I want to create a report in Reporting Services using > > this stored procedure as a datasource but it will not allow temp tables. I > > am currently using SQL2000. Please help. > > > > Thanks, > > Allison > > > > > >
I use temp tables all the time. You can only have one result set returned
from a stored procedure. The last statement do a select * from #temp (or whatever your temp table is called) and it will work. When you use the stored procedure you will need to be able to execute it to get the field list (and use the refresh fields button to see the list of fields). Bruce L-C [quoted text, click to view] "Ha Vo" <ha.vo@prometheuslabs.com> wrote in message news:Ojy6kLrbEHA.384@TK2MSFTNGP10.phx.gbl... > I am using table variables with all the tables in my query except one table > ( person table). This person table is a huge table...hence when I use all > table variables, the query takes 9 minutes plus to return data. Please help. > Thanks. > > Allison. > > > "B. Mark McKinney" <BMarkMcKinney@discussions.microsoft.com> wrote in > message news:17D7CE0D-2BD1-45E8-B80E-8AC7A696E9A9@microsoft.com... > > Try re-writing your stored procedure to use table variables only and not > use temp tables or create table statements. > > > > "Ha Vo" wrote: > > > > > Hello, > > > > > > I am using a stored procedure that's using table variables and a temp > table > > > for querying purposes. I want to create a report in Reporting Services > using > > > this stored procedure as a datasource but it will not allow temp tables. > I > > > am currently using SQL2000. Please help. > > > > > > Thanks, > > > Allison > > > > > > > > > > >
Bruce or Allison: The method that Bruced described on how to use the Temp table is exactly what I am currently using. And Yes, it works very well BUT I would like to learn how to use table variables. Do you have a sample code or a URL to the table variables? Please and Thank You! Augusta [quoted text, click to view] "Bruce Loehle-Conger" wrote:
> I use temp tables all the time. You can only have one result set returned > from a stored procedure. The last statement do a select * from #temp (or > whatever your temp table is called) and it will work. When you use the > stored procedure you will need to be able to execute it to get the field > list (and use the refresh fields button to see the list of fields). > > Bruce L-C > > "Ha Vo" <ha.vo@prometheuslabs.com> wrote in message > news:Ojy6kLrbEHA.384@TK2MSFTNGP10.phx.gbl... > > I am using table variables with all the tables in my query except one > table > > ( person table). This person table is a huge table...hence when I use all > > table variables, the query takes 9 minutes plus to return data. Please > help. > > Thanks. > > > > Allison. > > > > > > "B. Mark McKinney" <BMarkMcKinney@discussions.microsoft.com> wrote in > > message news:17D7CE0D-2BD1-45E8-B80E-8AC7A696E9A9@microsoft.com... > > > Try re-writing your stored procedure to use table variables only and not > > use temp tables or create table statements. > > > > > > "Ha Vo" wrote: > > > > > > > Hello, > > > > > > > > I am using a stored procedure that's using table variables and a temp > > table > > > > for querying purposes. I want to create a report in Reporting Services > > using > > > > this stored procedure as a datasource but it will not allow temp > tables. > > I > > > > am currently using SQL2000. Please help. > > > > > > > > Thanks, > > > > Allison > > > > > > > > > > > > > > > > > >
i found i have the same problem with temp tables in stored procs. if you hit
the refresh button and then the ! button the stored proc will work eventhough you get an error when you initially setup the dataset. [quoted text, click to view] "Bruce Loehle-Conger" wrote:
> I use temp tables all the time. You can only have one result set returned > from a stored procedure. The last statement do a select * from #temp (or > whatever your temp table is called) and it will work. When you use the > stored procedure you will need to be able to execute it to get the field > list (and use the refresh fields button to see the list of fields). > > Bruce L-C > > "Ha Vo" <ha.vo@prometheuslabs.com> wrote in message > news:Ojy6kLrbEHA.384@TK2MSFTNGP10.phx.gbl... > > I am using table variables with all the tables in my query except one > table > > ( person table). This person table is a huge table...hence when I use all > > table variables, the query takes 9 minutes plus to return data. Please > help. > > Thanks. > > > > Allison. > > > > > > "B. Mark McKinney" <BMarkMcKinney@discussions.microsoft.com> wrote in > > message news:17D7CE0D-2BD1-45E8-B80E-8AC7A696E9A9@microsoft.com... > > > Try re-writing your stored procedure to use table variables only and not > > use temp tables or create table statements. > > > > > > "Ha Vo" wrote: > > > > > > > Hello, > > > > > > > > I am using a stored procedure that's using table variables and a temp > > table > > > > for querying purposes. I want to create a report in Reporting Services > > using > > > > this stored procedure as a datasource but it will not allow temp > tables. > > I > > > > am currently using SQL2000. Please help. > > > > > > > > Thanks, > > > > Allison > > > > > > > > > > > > > > > > > >
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |