SQL Server Performance Forum – Threads Archive

Can’t create report with Report services

I tried to create report with Report Services but if my stored procedure use temp table (ex: #Temp), I’ll get the error "There is an error in the query. Invalid object name ‘#Temp’). If the stored procedure doesn’t include temp table, it will be OK, the report could be created. I have no idea what’s going on here. if anybody can give me a hand? Thanks a lot.

Can you execute the specified stored procedure in the query analyzer without any errors?
Also post the query processed. In future kindly post the threads in the relevant forum only.
(moving to Reporting Services forum from ANalysis forum) Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Yes, I can execute the stored procedure on Query Analyzer and get the query result, everything looks fine, but if I click the option Â¡Â°Display estimated execution planÂ¡Â± on Query menu of Query Analyzer, I got same message Â¡Â°Invalid object name ‘#temp’Â¡Â±. I tried many times on different Server, itÂ¡Â¯s happened to any stored procedures which include temp table. Is it could be same problem which I got from Report Services? Thanks for your response. lliu

If you are specifying the temp table explicitly then you must ensure that table exists on the TEMPDB database. May check thishttp://www.sqlteam.com/item.asp?ItemID=2644 link about using OUTPUT with stored
procedures. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Try explicitly dropping your temp tables at the end of your stored procedure. That worked for me with that error in Reporting Services

]]>