got error when using stored procedure with temp table in it

I got an error message when i created a dataset using a stored procedure with temp table in it. The error is:

"could not generate a list of fields for the query. check the query syntax or click the refresh fields on the query toolbar. invalid object name ' #indented' ".

after I click on the refresh button, everything looks fine. But after I put the fields in the table, and go to the Preview, it is a blank report.

Does anybody know what happened?

3x a lot!

Your problem occurs because the sproc isn't returning metadata (column descriptions, etc.) from your temp table dependably -- Nothing that you did wrong, we just can't get meta data from an object which doesn't exist yet...The way we normally get metadata back from sprocs doesn't work when they return data from a temp table.

There are two thing you can try, although I think you've already done #1...

#1. Use the generic query designer, click "Refresh Fields", and you'll get a little dialog asking you for paramter info for your sproc...provide it.. finish your work, cross your fingers, move to layout view and then Preview

#2. In your sproc, return your data using a table variable vs. Select * from ##SomeTempTable

From what I read, you'll actually only have this problem in the designer...if you went ahead and plugged in the correct RDL for your fields manually and then deployed the report to the server, it would run fine...

|||Thank you!|||I also get an error because of a temp table, but this happens when creating data-driven subscription query: The

dataset cannot be generated. An error occurred while connecting to a

data source, or the query is not valid for the data source.

(rsCannotPrepareQuery) Invalid object name '##XX'. Can you help me with

this?|||

Unfortunately, you're running into a behavior you're not going to able to avoid...if you try and set up the subscription via Report Manager it (Report Manager) will call "PrepareQuery" (which we would expect to fail when dealing with temp tables) ...That's why you get your error message.

If you're really serious about using the temp table, you can still get a data-driven subscription working, BUT the price that you pay is you'll have to do it via code, using the Web Service API to do the work...If you go directly against the web service, you bypass Report Manager calling (and then failing against) "PrepareQuery"...and even when/if you get this working, you'll NEVER be able to edit the subscription in the UI, or you'll get the same errors again. So, you really should just try and whack the use of #temp tables in your scenario. :)

|||Thank you for your

answer. My temporary solution was this: when I validate the query I get

the error described above however if I

run the same stored procedure that is used in a query in let's say

management studio query and leave it open, the query in data driven

subscription web interface is validated succesfully. It works for now,

but I will try to get rid of the temp tables.

|||Ha! Great idea...Never would have thought of this!