If Exists...Drop Table not always working...
-
All,
Okay, this is weird. I've never had problems with this command in SQL 2000, but I am having problems in 2k5. Whether I use this command with Temp tables or real tables, sometimes it'll work and sometimes it won't and my next statement, a SELECT INTO, will fail because "the table already exists".
GRRRRR. Anyway, here's the code:
If exists (Select distinct name from sys.objects where name = 'MyTable')
Drop table MyTable;
Select Distinct Col1, Col2
into MyTable
from TableA;
Simple, right? Should work. But for some reason, even if the "If Exists" statement says "Commands completed successfully", it doesn't always drop the table. I've tried the command with a BEGIN..END surrounding the DROP TABLE statement, doesn't work. I've tried statement terminators, batch terminators. Doesn't work. The other day, a job failed because of that command. This job has been running for years without any problem and it just up and decided not to process the "If Exists" command properly anymore.
If anyone has any advice, I'd be eternally grateful. Or any articles they could point me to. Thanks,
-
maybe try the following using object_id
If
Object_Id('<dbname>.dbo.<table_name>') is Not Null Drop Table <table_name>
-
Dunno about real tables, but that won't work with temp tables. The name in sysobjects in tempdb is not the same as the name you use to reference the table. For temp tables, my preference is the follwing type of script
EXECUTE ('SELECT 1 FROM #MyTable')
IF @@Error = 0 -- Table exists
Drop Table #MyTable
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
NAVJOAT is on the right track, and Gail is right about the temptable having a name that is generated in the tempdb. This name includes the name of the temptable, and some underscores, ...
However if you use object_id in your database, but query the tempdb, like:
select object_id('tempdb..#lama')
it will tell you if a temporary table (#lama) for your session exists. It will not conflict with the temptables with the same name from other databases, or same database but other sessions. Of course, you cannot use object_name with this id later, since the id is from another db. Weird is the right word for it
Regards,
Andras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software -
Of course, you cannot use object_name with this id later, since the id is from another db.
Actually, you can. At least in 2005 SP2. Object_Name has been enhanced to take a second parameter, the database id.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Nice,
Although it will return the generated name (with the underscores and the number at the end), so you would need to get rid of that part. (And I know only a few people who would use underscores at the end of temptable names.) But this is of course much better than nothing.
Andras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software -
Actually, it does...
IF OBJECT_ID('TempDB..#tablename','U') IS NOT NULL
DROP TABLE #tableName
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I still don't understand why ObjectID() would make a difference with non-temporary tables. It just happened to me again....Sortof. Now in the opposite way...
If exists (Select name from MyUserDB.sys.objects where name = 'SCR18556_PreFix')
Drop Table MyUserDB.dbo.SCR18556_PostFix;
The above code (written right above a Select...Into statement) just generated the error that it can't drop the table because it doesn't exist. Which is why I used "If Exists" in the first place, so I wouldn't get that frickin' error....
Though, the information about the new stuff the Temp tables are doing does explain a lot. But why is it giving me fits on real tables?
Sorry for the rant. I'm just a little frustrated when the same code works in a different spot with a different table name, but the instant I change 3/4 letters in the tablename, the code freaks out.
-
In your query above you check for PreFix, and you drop PostFix.
Could that be the problem? Or is it something that got in while copy/pasting here?
Regards,
Andras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software -
Andras,
I just noticed that myself... GAH! I'm working on so many bug fixes today that I feel like I left my brain at home. I am such a Ditz sometimes. @=/
Is it Friday yet?
-
My comment was directed at the OP, not the reply. Sorry for the confusion.
Select * from tempdb..sysobjects where name = '#tablename' won't return anything
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
"All things are difficult before they are easy."
Dr. Thomas Fuller (1654 - 1734), Gnomologia, 1732
I'm glad your problem is solved,
Andras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply