Retrieve fields schema in Visual Studio of stored procedure which uses temp tables

Question

I have some complex stored procedures pulling data from other databases using linked servers. This data is put into temp tables which are joined in a select query for output. The procedures work fine but in visual studio if I try to add the stored procedure to a dataset using the designer I get the error invalid object name #tmp or whatever the first temp table is called. It is unable to retrieve the database schema. It's the same for using and sqldatasource in ASP.NET.

The procedure is still usable but I have to manually add all the columns it should output to the datatable. This is going to be a pain to do manually and I assume it is to do with the way visual studio gathers the output fields from the stored procedure, it doesn't seem to run it in the normal way. Is there a way to correct this as I have quite a lot of these to do and don't want to have to add all the columns manually, which is time consuming and error prone.

Answer 1

After googling for a while I found a bizarre solution putting:

IF 1=0 BEGIN
SET FMTONLY OFF
END

At the start of your stored procedure allows the designer to correctly get the schema information. This code obviously never runs but it solves the issue. FMTONLY is related to only returning meta data about a stored procedure. I am using sql server 2005.

Answer 2

This works for me:

SET FMTONLY OFF

I Dont know why someone put in IF 1=0 in the other post? I think it was just to prove that when FMTONLY is ON that all conditions in IF / ELSE Statements are executed!

FMTONLY is typically turned on by applications such as SSRS and Visual Studio when refreshing the schema information for a stp. With FMTONLY on no statements are executed only the column names are returned. The issue is that because no statements are executed the temp table does not get created, and therefore the columns returned by any select statements on the temp table are unknown.

Setting FMTONLY ON will cause the stp to run fully. IF you have any insert / update / delete statements in your stp they will run, so capture the state of FMTONLY before you turn it OFF and take appropriate measures to prevent updates.

You can determine if FMTONLY is on by checking if 1 = 0. If it does then FMTONLY is on and you could just run a simple select of the columns that your stp normally returns.