Temp Tables
-
Hi,
I have a query which uses a temp table to store results of another query in. the thing is, it works fine when i use SQL Query Analyzer but when i put it into Reporting Services i get the following error:
---------------------------
Microsoft Development Environment
---------------------------
Could not generate a list of fields for the query.
Check the query syntax or click Refresh Fields on the query toolbar.
Invalid object name '#t'.
Invalid object name '#t'.
---------------------------
OK
---------------------------
Any help will be very much appreciated
Thanks
-
In my experience reporting services has a lot of trouble with temporary tables. If possible wrap the code into a stored procedure and have RS execute the procedure.
-
I think this is related to the scope of Local # temp table because if you change it to global ## temp table it works. SQL Server will run the code as local temp table but reporting services uses only global temp tables most of the time.
Kind regards,
Gift Peddie -
Hi,
I tried using ## with no luck. heres the code
--------------------
DECLARE @sql varchar(1000) DECLARE @stat varchar(10) DECLARE @date varchar(20) DECLARE @tg varchar(10) CREATE TABLE ##t(ref varchar(12)
collate Latin1_General_CI_AS)
SET @stat = '"Poor"'
SET @date = '"December2006"'
SET @tg = '"A 1-"'
SET @sql = '
INSERT into ##t (ref) SELECT studentID FROM OPENQUERY(MSQLREVIEW,''SELECT studentID,student FROM Reports
WHERE (((`classwork` = '
+ @stat + ' or `homework` = ' + @stat + ' or `midterm` = ' + @stat + ' ) AND `session` = ' + @date + ') AND `TG` = ' + @tg + ')
GROUP BY studentID
HAVING count(*)>=3
ORDER BY TG ASC'')'
EXEC (@sql)
SELECT t2.s_studentreference [Student reference], t1.p_forenames [Forenames], t1.p_surname [Surname]
FROM ##t A1, capd_person t1 LEFT OUTER JOIN
capd_student t2 ON t2.s_id = t1.p_id
WHERE A1.ref = t2.s_studentreference DROP TABLE ##t
-------------------
probably just going to put it in a function if i can figure out how to because it said i cant use a temp table in one
.thanks for all the help.
-
Convert it to a stored procedure and it will run assuming you are using 2005 I have seen worse code that run.
Kind regards,
Gift Peddie -
are you saying my coding skills are bad... lol i would have to agree with you.thanks, will give it ago.
-
One thing you can do if you,
1. create either actual tables or ## tables exactly like the temp table you want to use.
2. temporarily change your script to refer to those tables.
3. 2. in reporting services, in the datasource definition, refresh the datasource. Reporting Services will refresh the field list.
4. Continue with report design.
5. Change the script to refer to the # tables again and save the report.
6. drop the tables creasted in #1.
If anyone sees aproblem with this, let me know. But it's worked for me.
smv929 -
I did not say that but you are doing OUTER JOIN to a temp table in SSRS which gets confused with minor changes in a stored proc.
Kind regards,
Gift Peddie -
i didnt write that bit. its from a program we use that creates the sql for certain things. once i have everything working iam going to try and clean it all up. thanks for your help, just hope it doesnt take to long to finish everything
-
The reason to use stored proc is to force SSRS to use the code because stored proc is controlled by the relational engine SSRS just have to run it.
Kind regards,
Gift Peddie -
I have had the same issue\error message even with using temp tables in stored procedures. My solutions were to:
1) Use a @table variable instead of actual #temp table for relatively small result sets\resource usage.
2) For large result sets or intensive queries where performance might be a concern I use a similar approach as smv929 where I first create actual #temp tables with clustered keys and covering indexes to get the result set I want, then comment out the #temp table references and change them to @table variable references and develop the SSRS report against this so the SSRS designer doesn't barf with the unrecognized result set, and after THAT works and the SSRS report deployed go back and re-activate the #temp tables in the stored procedure for performance and witout touching it the SSRS report will still work. This needs to be communicated to all who come after you (at minimum as comments in the SP) to avoid extended head-scratching when they try to change the SSRS report.
As a rule also I always use stored procedures as SSRS data sources.
maddog -
I did the same thing as smv929 and it worked like a charm. For some reason the Report Wizard doesn't like temp (#) tables. Just let your sp create a permanent table during report creation then dump them after you are done creating the report. Making sure you change your sp to look for the temp tables, of course.
-
Have you tried using derived tables in place of temporary tables? I use derived tables in SSRS and they work fine. Also, derived tables have lower overheads in comparison to temporary tables.
-
Andrew Terwiel (1/9/2008)
Also, derived tables have lower overheads in comparison to temporary tables.Not really... both are formed in memory if there's room and in TempDB if not...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply