Last post Jan 27, 2014 10:58 PM by jjthebig1
Apr 16, 2007 10:00 PM|ianmain|LINK
Summary: When my stored procedure uses temporary tables then the TableAdapter won't be able to work out the field names and so won't work. I get an error in the TableAdapter configure wizard saying:
Invalid object name '#TempTable'.
I'm not doing anything unusual so this must be a common problem. Let me explain:
I'm using Visual Studio 2005 and SQL Server 2000.
Detail: I've written a new stored procedure (SP) that uses a temporary table in calculating the resulting results set (several fields with several rows). I recon the temporary table bit is significant.
I've created a new DataSet in VS2005 and dragged the stored proc onto the DataSet design surface.
I right click on the TableAdapter and enter the 'configure'. The problem is that the wizard doesn't think any fields are being returned by the SP.
If I try and do it another way I get the same problem: Right click on DataSet and add new TableAdapter (same thing happens, it won't recognise that there are fields being returned from the SP).
FYI: If I do it for an SP that doesn't use any temporary tables it all works like a dream (problem is that I need to use temporary tables as its complex [Nope] ).
Thanks for any advise
Apr 17, 2007 05:33 AM|jpazgier|LINK
If you returning results from your stored procedure as
select * from #tempTable
try to return it using column names it help sometimes
select column1,column2..... from #temptable
or try to do select from select
select * from (select * from #tempTable) temp
select column1,column2..... from (select * from #tempTable) temp
If it will not work you have to modify your procedure to not use Temporary table or configure your table adapter by hands.
May 02, 2007 04:43 PM|pretzelsteelersfan|LINK
There's an issue with tableadapters and temp tables. This thread has some info and links.
I simply used a Table var instead of a temp table and the issue was solved.
Nov 15, 2007 01:33 PM|bisdamonl|LINK
This works for me. Add these lines to the beginning of your sp:
IF 1=0 BEGIN
SET FMTONLY OFF
Obviously that statement is never executed, but for whatever reason it gets the job done.
Apr 02, 2010 11:21 AM|smm0314|LINK
Thanks bisdamonl. This worked like a charm!
Mar 07, 2011 01:46 PM|creagan|LINK
I was having a similar issue and this fixes it, but it may cause other issues.
Please see the following for more info:
Jul 26, 2012 07:57 PM|pizzamaker74|LINK
Man that is the wierdest thing.
Jan 27, 2014 10:58 PM|jjthebig1|LINK
It doesn't make sense, but it works!!!