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?
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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With