12

I have a stored procedure that I'm using in a dataset to generate a report in ReportViewer.

This SP uses temporary tables to store intermediate values, so that these can be used in a calculation at the end of the SP.

The temp tables are all dropped cleanly at the end of the SP.

I can execute the SP in SSMS with no issues and it returns the data that I expect.

However, when use the TableAdapter Configuration Wizard to update my xsd in VS2012, it gives me the error Invalid object name '#Held' (where #Held is the name of one of the temp tables).

What's going on?

Tom Studee
  • 10,316
  • 4
  • 38
  • 42
Kev
  • 2,656
  • 3
  • 39
  • 63
  • possible duplicate of [Why does my typed dataset not like temporary tables?](http://stackoverflow.com/questions/921864/why-does-my-typed-dataset-not-like-temporary-tables) – Adam Nofsinger Oct 07 '14 at 19:12

2 Answers2

28

The answer stated here works perfectly, for reasons unknown.

Just place the below code after the stored procedure after the AS part of the SP.

IF 1=0 BEGIN
    SET FMTONLY OFF
END
Community
  • 1
  • 1
Bat_Programmer
  • 6,717
  • 10
  • 56
  • 67
  • Worked like a charm. FMTONLY returns only metadata to the client. Can be used to test the format of the response without actually running the query. – Ankit Suhail Jun 21 '17 at 07:38
  • You are my hero right now Bat. I've been banging my head against the wall for the past 2 hours trying to figure out why temp tables in an SP cause the TableAdapter Config Wizard to fail. – John Baker Sep 27 '17 at 18:21
  • 1
    years later you saved someone's day today, know that. :) – Eray Balkanli Jul 12 '19 at 15:15
2

There's some known issues with #temp tables and table adapters.

Some people have got around it by explicitly selecting column names, eg:

SELECT column1, column2, ... from #temptable rather than SELECT * (if you are doing that)

You could also try using a table variable rather than a temp table.

Tom Studee
  • 10,316
  • 4
  • 38
  • 42