Why does my typed dataset not like temporary tables?

tsql stored-procedures visual-studio-2008 strongly-typed-dataset sql-server-2005-express
Malfist picture Malfist · May 28, 2009 · Viewed 8.3k times · Source

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?

Answer

Malfist picture Malfist · May 28, 2009

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.

Related questions

Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
Select columns from result set of stored procedure

I have a stored procedure that returns 80 columns, and 300 rows. I want to write a select that gets 2 of those columns. Something like SELECT col1, col2 FROM EXEC MyStoredProc 'param1', 'param2' When I used the above syntax I …

Read More
Function vs. Stored Procedure in SQL Server

I've been learning Functions and Stored Procedure for quite a while but I don't know why and when I should use a function or a stored procedure. They look same to me, maybe because I am kinda newbie about that. …

Read More