When you drop a column from a transactional or a merge replication published table, SQL Server 2000 does not drop the column, and you may receive the following error message:
Server: Msg 5074, Level 16, State 1, Line 1 The object '<constraint name> ' is dependent on column ' <column name> '. Server: Msg 4922, Level 16, State 1, Line 1 ALTER TABLE DROP COLUMN <Column name> failed because one or more objects access this column.
There is a constraint on the column that you tried to drop.
To work around the problem, remove all the constraints that are defined on the column that you want to drop. For example:
Make a note of all the constraints that are defined on the column that you want to drop.
Note When you try to drop the column (that has a constraint) from a published table by using the sp_repldropcolumn stored procedure, the list of objects dependent on the column are displayed in the error message. These are the constraints that are defined on the column.
At the publisher, create a script to remove all the constraints that are defined on the column you want to drop from the published table.
For example, to create a script to drop a constraint, follow these steps:
Paste the following Transact-SQL command:
Note Replace the PublishedTableName and ConstraintName with the name of the published table and the constraint name respectively.
Save the file as DropConstraint.sql.
At the publisher, run the DropConstraint.sql script by using the osql command prompt utility. To do so, follow these steps:
Open a command prompt.
Locate the <Installation drive>:\Program Files\Microsoft SQL Server\80\Tools\Binn folder.
At the command prompt, type the following osql command: osql -S <serverName> -U <userName> -P <password> -i <path Of DropConstraint.sql> -d <publicationDatabase>
If the replication type is merge replication, you must drop the constraints at the subscriber. To do so, run the following Transact-SQL command in SQL Query Analyzer at the publisher:
At the publisher, drop the column from the published table:
To make schema changes to a published table, you must use either the:
Replication properties dialog box in SQL Server Enterprise Manager.
Replication stored procedures.
SQL Server 2000 does not provide replication stored procedures to make the schema changes on the published table with respect to constraints. Therefore, you must use the Transact-SQL ALTER TABLE statement to add or remove the constraints that are defined in the published table.
Changes made to the published table through an ALTER TABLE Transact-SQL statement are not reflected at the subscriber. Therefore, you must explicitly propagate to the subscribers the changes that you make to the schema of a published table by using the Transact-SQL ALTER TABLE statement.
NOTE Although this article talks about receiving the errors mentioned in the SYMPTOMS section for transactional or merge articles, this should apply to non-replicated tables also as long as the columns we are trying to drop have constraints in them.
Consider the following Transact-SQL statements that create a table called test in the Pubs database:
Use Pubs Go create table test (c1 int primary key, c2 int) Go
If you try to drop column c1 by running the following code, you will receive the following error message because column c1 has a primary key constraint defined on it:
alter table test drop column c1
Server: Msg 5074, Level 16, State 8, Line 1 The object 'PK__test__20E1DCB5' is dependent on column 'c1'.
Server: Msg 4922, Level 16, State 1, Line 1 ALTER TABLE DROP COLUMN c1 failed because one or more objects access this column.
For additional information about schema changes, visit the following Microsoft Web site: