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

The reason it cannot read metadata is because you have a #Temp table and it cannot find the temp table. You can try to get around it by defining the metadata using a TOP 0 query

SELECT TOP 0
    CONVERT (NULL, VarChar (30)) AS Column1,
    CONVERT (NULL, INTEGER) AS Column2,
    CONVERT (NULL, DECIMAL (9, 2)) AS Column3,
    CONVERT (NULL, VarChar (55)) AS Column4

UNION

SELECT *
FROM #MyTempTable

Answer 2

The reason you cannot obtain meta data here is because it could depend on data. For example.

If Exists(Select 1 From Table Where Column1 = 7)
  Select Col1, Col2 From Table2
Else
  Select Col3, Col4, Col5 From Table2

I would never write a stored procedure that has a different number of columns depending on data, BUT it is possible to do. Just for a moment, suppose you had a stored procedure like this. What column list should be returned?

Answer 3

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.