Geek Answers Handbook

Table adapter configuration wizard does not like temporary tables in SP

I have a stored procedure that I use in a dataset to create a report in ReportViewer.

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

Temporary tables are completely deleted at the end of the SP.

I can execute the SP in SSMS without problems, and it returns the data that I expect.

However, when using 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 temporary tables).

What's happening?

+7
source share
2 answers

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

Some people will get around by explicitly choosing column names, for example:

SELECT column1, column2, ... from #temptable , not SELECT * (if you do)

You can also try using the table variable rather than a temporary table.

0
source

The answer given here works fine, for unknown reasons.

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

 IF 1=0 BEGIN SET FMTONLY OFF END
+18
source

More articles:


All Articles