October 3, 2012 at 6:11 am
This one is really annoying. I'm in our Dev environment, trying to do an ALTER TABLE script. The columns have a Default Constraint on them that needs to be dropped & recreated, so I went into the GUI (the very database that I'm working in) and scripted out the constraints as DROP & CREATE.
When I try to run the DROP code, it's not dropping the constraint, even though the message says "command(s) completed successfully." When I run just the ALTER TABLE ... DROP CONSTRAINT code (without the IF EXISTS check), the constraint drops just fine. Does anyone know if there's a problem in 2k8 that would prevent Microsoft's own code from working correctly?
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_MyConstraintName]') AND type = 'D')
BEGIN
ALTER TABLE [MySchema].[MyTable] DROP CONSTRAINT [DF_MyConstraintName]
END
Even changing the IF EXISTS code doesn't seem to work. (This is my standby when IF EXISTS doesn't seem to work).
IF (SELECT Name FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_MyConstraintName]') AND type = 'D') IS NOT NULL
BEGIN
ALTER TABLE [MySchema].[MyTable] DROP CONSTRAINT [DF_MyConstraintName]
END
BTW: We create our own constraint names so they are consistent across environments rather than use the auto-assigned SQL Server names.
SQL Server Version: Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64) Sep 16 2010 19:43:16 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)
Thoughts?
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 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply