Why does my typed dataset not like temporary tables?

Question

I am attempting add a tableadapter to a stored procedure in my SQL Server 2005 Express. The stored procedure, however, uses a temporary table called #temp. When creating the table adapter, Visual Studio complains "Unknown Object '#temp'" and says that the stored procedure returns 0 columns. This is problematic because I use that stored procedure with a crystal report, and need those columns.

How can I fix this?

Fate

Answer 1

Bizarre. According to this you add

IF 1=0 BEGIN
    SET FMTONLY OFF
END

to the SP right after the AS part of the SP and it works. Visual Studio now has no problem with it. I have no idea why this works like this, or why it would work, but it does.

Fate

Someone probably having a bad hairday. It helped me. +1 – Shortwave

all I can say is... wow. this works. This is a major big deal for me. I use temp tables that need to be unique for each instance of a report that is run. Can't have tables getting overwritten from another workstation. I needed the temp tables to work with my stored procedure. Thanks. – Ejectment

Answer 2

This may be an old thread and the answer is found, but when someone gets into your stored procedure after and see this code, he really does not understand. There is another way to do this properly and it is to simply declare the table as a variable like this :

DECLARE @temp TABLE  
(
    SomeText1 nvarchar(255),
    SomeText2 nvarchar(255)
)

Also, don't forget to remove the DROP TABLE at the end.

PS : If you really need to use the temporary table because you need to create it, then you have to write the code given in the previous answer. Hope this helps.

Veritable