Server: Msg 208, Level 16, State 1, Invalid object name '#temptbl'

Posted on 2005-03-10
Medium Priority
Last Modified: 2012-05-05
A developer asked me to look at why a stored proc was leading to timeouts on the app.   I copied the sproc into QA, stripped out the 'create procedure' stuff and ran the code.   It runs but takes over a minute to complete (no wonder considering the way it is written).  Anyway, I want to look at the execution plan but as soon as I do this, I get the above error.   I can see the temp table in the code and I can't see anything wrong with the way it is used and, in any case, the code works.   Can anyone explain what causes this problem and what can be done to remove the problem so I can see the execution plan?

Question by:AustinSeven
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3

Assisted Solution

Viggee earned 800 total points
ID: 13504771
when a temp table with # is used, u cannot use the execution plan...

replace the temp table with actual table to get the execution plan.
LVL 23

Accepted Solution

adathelad earned 1200 total points
ID: 13504773
For the purposes of seeing the execution plan, change the temp table to a physical table (you can delete the table after). AFAIK this is the only way round it.....this is just a "feature" of using temp tables
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 13504775
Maybe execution plan don't support temporary tables?
LVL 23

Expert Comment

ID: 13504784
I get the same issue when I am doing a DTS export of data to CSV - I originally populated a temp table in stored proc, manipulated the data a bit and then returned the recordset from the temp table to be exported to CSV - but had to change this to use a physical table as DTS gives the same error when trying to map fields to the output file
LVL 23

Assisted Solution

adathelad earned 1200 total points
ID: 13504801
I think it's because when SQL Server first analyses the statement(s) to generate the query plan, it doesn't actually run it - so the temp tables will not exist at that point (it will only exist when you actually run the procedure).

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question