October 3, 2012 at 6:14 am
Looks like an easy mistake, but you have [ ] around the object name in the IF EXISTS.
Scrap that it does work with [ ] brackets, in the object_id function.
Looking at it, your referencing the column type which is in sys.objects not dbo.sysobjects, it should be xtype instead.
October 3, 2012 at 6:20 am
anthony.green (10/3/2012)
Looks like an easy mistake, but you have [ ] around the object name in the IF EXISTS.Scrap that it does work with [ ] brackets, in the object_id function.
Looking at it, your referencing the column type which is in sys.objects not dbo.sysobjects, it should be xtype instead.
Nope. Type is in sysobjects, just near the end.
I've narrowed it down to a problem with OBJECT_ID not returning an OBJECT_ID no matter what version of the name I tried.
SELECT Name, OBJECT_ID(N'DF_MyConstraintName')
FROM dbo.sysobjects
GO
SELECT Name, OBJECT_ID('DF_MyConstraintName')
FROM dbo.sysobjects
GO
SELECT Name, OBJECT_ID(N'[DF_MyConstraintName]')
FROM dbo.sysobjects
GO
SELECT Name, OBJECT_ID('[DF_MyConstraintName]')
FROM dbo.sysobjects
GO
All these options return the same thing: DF_MyConstraintName, NULL.
Is there a bug with the OBJECT_ID() function?
October 3, 2012 at 6:28 am
Yep, noticed the type column while investigating it, always thought the two view where different in that way, obiously not.
It is down to the schema which is why it wouldn't find the object id.
I ran the following
create schema ant authorization dbo
create table ant.tab1 (id int default(0))
select * from dbo.sysobjects where type = 'd'
SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__tab1__id__7E6CC920]') AND type = 'd'
SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[ant].[DF__tab1__id__7E6CC920]') AND type = 'd'
The first select returned nothing, the second did return the actual object id.
October 3, 2012 at 6:37 am
Well, that is annoying. So Microsoft can't be bothered to add the schema into their own auto-scripted stuff?
<headdesk>
Thanks, Anthony. I guess I'm hand typing all these DROP & CREATE scripts myself since the script code can't seem to handle this sort of thing.
October 3, 2012 at 6:49 am
So far I believe it is only an issue on default constraints, as when you script a table for drop and create it picks up the schema of the table.
I will check in 2012 to see if it is still the same, but might be worth raising a connect issue on it if not.
October 3, 2012 at 6:57 am
Right so just did exactly the same again, but in 2012 is seems like they dont wrap the drop or create in IF EXISTS commands, and just does the ALTER TABLE .......... DROP CONSTRAINT ............ without checking if it exists first so it seems they have got around the fault that way.
October 3, 2012 at 7:12 am
Not knowing 2012 from boo...
Why would they not want to check for existence before dropping something?
Does it not cause errors when running a DROP for a non-existant object?
October 3, 2012 at 7:23 am
Unsure on the way that MS's brain works, but I would be guessing that if they think that if the user has scripted the DROP and CREATE from within Object Explorer that the object must exist therefore don't bother checking for it first.
But it would seem like MS have know about this for a few years now, would of thought that would of gone in on a SP or a CU, but seems like they just reference it as fixed in the next release, which to me, the fix isn't all what it is all to be as what happens should two people do the same task a split second apart, the constraint wont exist and you would get an error.
Viewing 8 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply