Drop Column

  • 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

  • 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

  • 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

    bkelley@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/bkelley/

    K. Brian Kelley
    @kbriankelley

  • Thank you All for the Help!

    It works now after droping the constraints.

    Abdu

  • 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

  • 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

  • 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

  • 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

  • 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