SQL Server Performance

error in "Add/Drop column on replicated table"

Discussion in 'Performance Tuning for SQL Server Replication' started by santnair, Sep 7, 2005.

  1. santnair New Member

    Hi,

    I had to drop a column in a table.With reference to the error in the following lines, I was tring to drop a column which has a FK relatioship.The column(bigStockLocationId) got dropped by the sp_dropColumn.But there is a error at the merge agent.Merge agent failed.

    =======================================================================================
    The schema script 'exec sp_repldropcolumn '[dbo].[tblHardwareStock]', 'bigStockLocationId', 1' could not be propagated to the subscriber.
    (Source: Merge Replication Provider (Agent); Error number: -2147201001)
    ---------------------------------------------------------------------------------------------------------------
    A column was added to or dropped from the replicated table.
    (Source: myserverSUB4 (Agent); Error number: 27160)
    ---------------------------------------------------------------------------------------------------------------
    The object 'FK_tblStock_DMS_tblUniqueDO' is dependent on column 'bigStockLocationId'.
    (Source: myserverSUB4 (Data source); Error number: 5074)
    ---------------------------------------------------------------------------------------------------------------
    ALTER TABLE DROP COLUMN bigStockLocationId failed because one or more objects access this column.
    (Source: myserverSUB4 (Data source); Error number: 4922)
    ---------------------------------------------------------------------------------------------------------------

    =========================================================================================

    How will I drop the foreign key relationship along with the column


    thanks
  2. satya Moderator

    You need to drop the constraint before dropping the column using
    alter table drop constraint
    and refer to Books Online for more information.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. santnair New Member

    Hi satya thanks,
    I did drop the constraint as said, but it shows the same error ...


    Regards
  4. satya Moderator

    Use sp_repldropcolumn and if that doesn't work then you must drop the replication and then reconfigure.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. santnair New Member

    hi,
    Thanks. Yes ..., then
    sp_repldropcolumn was the thing , which was run initilly.
    Later only I got that error.Why did that happen.
    Ok, How to reconfigure the replication.what are the steps to be followed

    Regards

Share This Page