February 1, 2002 at 12:02 pm
Hi,
I have a push merge Replication (2SQL2K)
I'm trying to drop a column froma replicated table,
this column has default of NULL.
when I exceute the sp_repldropcloumn,it does not drop the column.
the error returned:
The object 'DF__Contacts__Label__0B335E0F' is dependent on column 'Label'.
ALTER TABLE DROP COLUMN Label failed because one or more objects access this column.
How can I drop a cloum if it has a default or a constraint?
Any Help will appreciated.
Abdu
February 1, 2002 at 12:51 pm
You'll have to drop the constraint also. To figure out what the constraint is, I usually script out the table. The constraint could also be the statistics.
Alter table drop constraint "constraint_name"
Good Luck
Tom Goltl
February 1, 2002 at 2:09 pm
In this case, the constraint is: DF__Contacts__Label__0B335E0F. I'm assuming the column was created with a DEFAULT value, because this looks like a SQL Server generated constraint name. However, as Tom has indicated, it would be a good idea to script out the table to ensure there aren't additional constraints. Also, run an sp_help to make sure there aren't other tables dependent on the column.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 1, 2002 at 4:25 pm
Thank you All for the Help!
It works now after droping the constraints.
Abdu
February 1, 2002 at 6:24 pm
Hi,
Just one thing that was not mentioned here, is when you Drop the constraints you have to drop them in BOTH publisher and subscriber database in order for the drop column to be successeful.
Abdu
February 1, 2002 at 9:37 pm
I had no idea such a stored procedure existed. We always stop replication and usually must remove the subscriber and publisher. This reafirms my belief that it is good to read posts even though I don't know the solution. Does this work for transactional replication too?
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 2, 2002 at 7:33 pm
It does. Its a feature new to SQL2000. Prior to that you had to work at it - I've got an article posted that offers a fairly straight forward method in SQL7.
Andy
February 7, 2002 at 11:08 am
Thanks! I just read the article and plan to experiment in our development environment.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 7, 2002 at 12:51 pm
Thanks, I have had troubles in the past and this helps.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply