ADO.NET Dataset MS SQL procedure: Invalid object name #TempTable

This is commonly known error, but if you try to google it then hardly you will find immediately result. Hope this page will have better index in search engine.

So you create procedure in MS SQL with temporary table, e.g.:

CREATE PROCEDURE my_proc

AS

BEGIN

    create table #TempTable(ID int)

    

   -- insert, select data, etc

  return select * from #TempTable 

END

An attempt to add tableAdapter in DataSet will show error:  Invalid object name '#MyTempTable'. 

The solution is to come around ADO.NET with FMTONLY statement, it does not allow to run procedure. Adding IF 1= 0 will not run inside MS SQL, but will run on ADO.NET request.

CREATE PROCEDURE my_proc

AS

BEGIN

   IF 1=0  -- hint for ADO.NET

   BEGIN

     SET FMTONLY OFF

   END

    create table #TempTable(ID int)

    

   -- insert, select data, etc

  return select * from #TempTable 

END