April 25, 2007 at 3:37 pm
I'm trying to build a report using the Report Wizard in Visual Studio 2005. Everything goes great until I put the "EXEC <stored procedure name>" in the "Query String" dialog box. I get the following error "There is an error in the query. Invalid object name '#tmpTable'." Well guess what?.......... if I go to the Query Builder button and execute the sp from there I get a freakin' recordset. If it can run the sp in the Builder, why in the world can it not work in the Report Wizard?
April 25, 2007 at 3:44 pm
If all the code to generate the recordset is inside a procedure, change the command type of the data set to "StoredProcedure" and just use the stored procedure name.
April 26, 2007 at 10:18 am
If, for some reason, you still need to use the "exec storedproc" syntax in the query window, I would suggest you create a dummy table with similar fields, and query that until you get your report set up the way you want.. then change the query text back to the original stored procedure and the report should run.
Do something like this in Query Analyzer:
create table mytable ([field definitions])
insert into mytable (field1, field2,...)
exec storedproc
Then use "select * from mytable" in the report query window; revert back to "exec storedproc" after you've got the report setup.
I've found that running the query in the report query editor does not work well with temp tables, dynamic sql and the like, so you often need this workaround.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply