• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 359
  • Last Modified:

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

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
AustinSeven
Asked:
AustinSeven
  • 3
3 Solutions
 
ViggeeCommented:
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
 
adatheladCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Maybe execution plan don't support temporary tables?
0
 
adatheladCommented:
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
 
adatheladCommented:
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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now