Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
doco
Yak Posting Veteran
77 Posts |
 Posted - 2010-05-11 : 17:21:31
|
| [code]IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fire_patrol_charges]') AND type in (N'U'))DROP TABLE [dbo].[fire_patrol_charges]GO[/code]AND[code]IF EXISTS ( SELECT * FROM tempdb.dbo.sysobjects o WHERE o.xtype IN( 'U' ) AND o.id = object_id( N'tempdb..#fpatrol_tmp' ) ) DROP TABLE #fpatrol_tmp;GO[/code]Can anyone tell me why neither one of these work? Using SQL Server 2005Education is what you have after you've forgotten everything you learned in school |
|
|
malpashaa
Constraint Violating Yak Guru
264 Posts |
Posted - 2010-05-11 : 18:22:54
|
OBJECT_ID works in the context of current database (if you did not specify the database). So to avoid such problem, you should provide the database name like this:IF OBJECT_ID(N'tempdb.dbo.fire_patrol_charges', N'U') IS NOT NULL DROP TABLE tempdb.dbo.fire_patrol_charges;GO Here I assumed the table exists in tempdb. |
 |
|
|
doco
Yak Posting Veteran
77 Posts |
Posted - 2010-05-11 : 23:23:37
|
| The top example is in the tempdb but the bottom is not. The problem seems to be in the placement of [ GO ]. I am away from my office at the moment so cannot show the exact script. The script creates a temp table then uses that temp table in a continuing script that creates a permanent table. I am not an accomplished script writer so am a bit frustrated by what is taking place with [ GO ]. The script eventually does what I intend for it to do but always leaves one error stating the last table already exists in the db. Anyway, I will give examples from the office in the morning if you wish to see them...Education is what you have after you've forgotten everything you learned in school |
|
|
|
|
|
|
DROP TABLE DOES NOT WORK