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
LVL 10
AustinSevenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.