Temporary table issue in Reporting Services 2000

Question

Hi,

I have just created a report in Report Manager using a Stored Procedure which uses a temporary table.

I'm getting the error "Could not generate a list of fields for the query. Invalid object name '#xxxx'"

I read this is because when Report Manager tries to PrepareQuery it runs into difficulty because the temporary table doesn't exist yet, is this correct? If so do you have any work arounds?

Thanks

Answer 1

The only way is an obvious one, make your temp table a permanent one and do the management of it yourself (deleting rows after use etc). This could be faster as well as indexable and you can add keys as needed to ensure integrity. It can also act as a cache for long running queries.

If you are certain that there will only ever be one user calling the sp then you can dump the data from the table using truncate rather than delete.

Not much help, but I never found any other way of doing it.

Answer 2

I don't really know much about reporting services, but that sounds accurate to me.

It sounds to me like you may not be able to use a temp table to store the data. You may need to use a permanent table instead.

I would ask myself why I was using a separate table anyway. Does the data already exist in other tables? Can I get it from there instead? Sometimes you don't have a choice in using a separate table, just make sure.

Answer 3

I don't know what exactly causes the problem, but you could try using a variable table.

declare @table table (Field int)

Answer 4

I ran into this one once...exact same thing, actually. I fixed it by dropping this line into my SP before creating any temp tables:

SET FMTONLY OFF

That should do the trick for it.

Answer 5

I tried "SET FMTONLY OFF" and it worked a treat. I am using BIDS 2005 and encountered the same issue. Thank you. PS: I tried "voting up" GUS's answer but couldn't - not enough reputation yet - how ridiculous.