Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

TableAdapter Configuration Wizard doesn't like temp tables in SP

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?

like image 586
Kev Avatar asked Jun 28 '13 15:06

Kev


2 Answers

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
like image 180
Bat_Programmer Avatar answered Nov 07 '22 08:11

Bat_Programmer


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.

like image 25
Tom Studee Avatar answered Nov 07 '22 09:11

Tom Studee