t-sqlstored-proceduresvisual-studio-2008strongly-typed-datasetsql-server-2005-express

Why does my typed dataset not like temporary tables?

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?

Solution

  • 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.