Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2005-03-10
5
Medium Priority
?
356 Views
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?

AustinSeven
0
Comment
Question by:AustinSeven
[X]
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
5 Comments
 
LVL 5

Assisted Solution

by:Viggee
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.
0
 
LVL 23

Accepted Solution

by:
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
0
 
LVL 52

Expert Comment

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

Expert Comment

by:adathelad
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
0
 
LVL 23

Assisted Solution

by:adathelad
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).
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

636 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