locked
TableAdapter can't see stored proc returned fields when using temp table RRS feed

  • Question

  • User1156234702 posted

    Hi,

    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

    Monday, April 16, 2007 10:00 PM

Answers

  • User1866903671 posted

    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

    or

    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.

    Good luck

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, April 17, 2007 5:33 AM

All replies

  • User1866903671 posted

    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

    or

    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.

    Good luck

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, April 17, 2007 5:33 AM
  • User241235865 posted

    There's an issue with tableadapters and temp tables. This thread has some info and links.

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=121116&SiteID=1

     I simply used a Table var instead of a temp table and the issue was solved.

    HTH,

    Paul

     

    Wednesday, May 2, 2007 4:43 PM
  • User787128076 posted

    <link href="http://forums.microsoft.com/MSDN/WebResource.axd?d=NySzF1eivP_rMoc50GQJzcvS4MHMOEKwYrCIgDtzuznEdiAYyKQ4jovGJOr6HWDM0&t=633263991144971555" rel="stylesheet" type="text/css">This works for me.  Add these lines to the beginning of your sp:

    IF 1=0 BEGIN
        SET FMTONLY OFF
    END

    Obviously that statement is never executed, but for whatever reason it gets the job done. 

    Thursday, November 15, 2007 1:33 PM
  • User104952650 posted

    Thanks bisdamonl.  This worked like a charm! 

    Friday, April 2, 2010 11:21 AM
  • User1326538793 posted

    I was having a similar issue and this fixes it, but it may cause other issues.

    Please see the following for more info:

    http://www.sqlservercentral.com/Forums/Topic969503-338-1.aspx

    Monday, March 7, 2011 1:46 PM
  • User-10017267 posted

    Man that is the wierdest thing.

    Thursday, July 26, 2012 7:57 PM
  • User-1602234753 posted

    Thanks, bisdamonl.

    It doesn't make sense, but it works!!!

    Monday, January 27, 2014 10:58 PM