Thursday, March 29, 2012

dropping default constraints

Hello

We've got a product which uses merge replication with anonymous pullsubscriptions.
At most custome sites it's running on SQL Server 2000, a few with SQL 2005, which is running wich replication compatibility level 80 due to .
As it happens, db schema changes. So I have to drop a column with a default constraint. First the constraint, then the column.
This works excellently on the publisher - but not on the subscriber Sad

The schema script 'exec sp_repldropcolumn '[dbo].[role_modul_rmd]', 'rmd_modul_enabled', 1' could not be propagated to the subscriber. (Quelle: MSSQL_REPL, Fehlernummer: MSSQL_REPL-2147201001)
Hilfe abrufen: http://help/MSSQL_REPL-2147201001
The object 'DF__role_modu__rmd_m__3119DB2C' is dependent on column 'rmd_modul_enabled'. (Quelle: MSSQLServer, Fehlernummer: 5074)
Hilfe abrufen: http://help/5074
ALTER TABLE DROP COLUMN rmd_modul_enabled failed because one or more objects access this column. (Quelle: MSSQLServer, Fehlernummer: 4922)
Hilfe abrufen: http://help/4922

What can bi done?

Thanks for your advice
Aline

We had this exact same problem.

First to recover your replication you can go in and drop the column manually on the subscribers. Then replication should recover.

This happens because replication does not deliver the commands in the correct order, it tries to drop the column first before droping the constraint which it cannot do. The only solution is to drop the constraint first then wait for everyone to replicate then try dropping the column.

Martin

No comments:

Post a Comment