November 12, 2009 at 6:51 am
I have a report that takes a long tim e to run (20 min)
Problem is some of my users don't wait for it to finish, and it uses temp tables. So, when the next user comes along they get a temp table error. If I go in and drop the table:
DROP TABLE ##RIM14
DROP TABLE ##RESULTS14
Then they can run the report.
How can I check (in code) if my two temp tables are there, and if so, I need to drop them? If they don't exist, then don't do anything, run the rest of my query, if they do exist then drop them and run the rest of my proc (which creates them)
November 12, 2009 at 7:13 am
Is this what you want at the top of your stored procedure?
IF OBJECT_ID( 'tempdb..#urtemptable') IS NOT NULL
DROP TABLE #urtemptable
---------------------------------------------------------------------------------
November 12, 2009 at 7:25 am
Thanks Nabha!
Exactly what I needed.
Good karma to you.
November 12, 2009 at 7:39 am
u r welcome 🙂
---------------------------------------------------------------------------------
November 12, 2009 at 11:52 am
Make sure you are using local temp tables instead of global temp tables. Local temp tables are preceded by a single # - global temp tables are preceded by two #.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply