Merge replication - delete field

Posted on 2003-03-25
Medium Priority
Last Modified: 2012-06-27
I have a merge replication and I try to delete a field without to stop replication(I supposed to be able to do this using SQL Server 2K) from enterprise manager.

Error Message: "The object ... is dependent on column ....; ALTER TABLE DROP COLUMN ... failed because one or more objects access this column."

The dependent object is the default constraint for that column.

What should I do to delete the column?

Any ideas?

Thank you.
Question by:toteu
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 34

Expert Comment

ID: 8207492
"The dependent object is the default constraint for that column".  Have you tried dropping the constraint first and then dropping the column?


Author Comment

ID: 8208098
I was hopping that I will be able to find a guy who actually did it; learn from somebody's mistake instead to make one right?

In BOL they say that, with a few exeptions, when you drop the column, the associated constraints will be droped too; why is not working? I made some research and it looks like I'm not the only guy with this problem.

LVL 34

Accepted Solution

arbert earned 500 total points
ID: 8208148
Ahhhh, ok I see.  You can't just drop the column with an alter database statement.  You need to use the sp_repldropcolumn  stored proc....

From Books Online:

sp_repldropcolumn [ @source_object = ] 'source_object'
    , [ @column = ] 'column'
    [ , [ @schema_change_script = ] 'schema_change_script' ]
    [ , [ @force_invalidate_snapshot = ] force_invalidate_snapshot ]
    [ , [ @force_reinit_subscription = ] force_reinit_subscription ]

[@source_object =] 'source_object'

Is the name of the table article that contains the column to drop. source_object is nvarchar(258), with no default.

[@column =] 'column'

Is the name of the column in the table to be dropped. column is sysname, with no default.

[@schema_change_script =] 'schema_change_script'

Is the path to the SQL script. schema_change_script is nvarchar(4000), with a default of NULL.

[@force_invalidate_snapshot = ] force_invalidate_snapshot

Enables or disables the ability to have a snapshot invalidated. force_invalidate_snapshot is a bit, with a default of 1. 1 specifies that changes to the article may cause the snapshot to be invalid, and if that is the case, a value of 1 gives permission for the new snapshot to occur. 0 specifies that changes to the article will not cause the snapshot to be invalid.

[@force_reinit_subscription = ] force_reinit_subscription

Enables or disables the ability to have the subscription reinitializated. force_reinit_subscription is a bit with a default of 0. 0 specifies that changes to the article will not cause the subscription to be reinitialized. 1 specifies that changes to the article may cause the subscription to be reinitialized, and if that is the case, a value of 1 gives permission for the subscription reinitialization to occur.

Return Code Values
0 (success) or 1 (failure)

sp_repldropcolumn is used for all types of replication.

When using sp_repldropcolumn, if a schema change is made to an article that belongs to a publication that uses a DTS package, the schema change is not propagated to the Subscriber, and the custom procedures for INSERT/UPDATE/DELETE are not regenerated on the Subscribers. The user will need to regenerate the DTS package manually, and make the corresponding schema change at the Subscribers. If the schema update is not applied, the Distribution Agent may fail to apply subsequent modifications. Before making a schema change, make sure there are no pending transactions to be delivered. For more information, see How Transforming Published Data Works.

Important  A backup of the publication database should be performed after sp_repldropcolumn has been executed. Failure to do so can cause a merge failure after a restore of the publication database.


Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question