PRB: Error 5074 occurs when you try to drop a column from a published table (816755)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)

SYMPTOMS


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.

CAUSE

There is a constraint on the column that you tried to drop.

WORKAROUND

To work around the problem, remove all the constraints that are defined on the column that you want to drop. For example:
  1. 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.
  2. 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:
    1. Open Notepad.
    2. Paste the following Transact-SQL command:

      alter table <PublishedTableName> 
      drop constraint <ConstraintName>
      go

      Note Replace the PublishedTableName and ConstraintName with the name of the published table and the constraint name respectively.
    3. Save the file as DropConstraint.sql.
  3. At the publisher, run the DropConstraint.sql script by using the osql command prompt utility.
    To do so, follow these steps:
    1. Open a command prompt.
    2. Locate the <Installation drive>:\Program Files\Microsoft SQL Server\80\Tools\Binn folder.
    3. At the command prompt, type the following osql command:
      osql -S <serverName> -U <userName> -P <password> -i <path Of DropConstraint.sql> -d <publicationDatabase>
  4. 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: 

    use <PublicationDatabase>
    go
    sp_addscriptexec @publication = <PublicationDatabase>,
    @scriptfile = <path of DropConstraint.sql>
    go
  5. At the publisher, drop the column from the published table:

    use <PublicationDatabase>
    go
    exec sp_repldropcolumn 
    @source_object = <PublishedTableName>,
    @column = <columnName>
    go

MORE INFORMATION

To make schema changes to a published table, you must use either the:
  • Replication properties dialog box in SQL Server Enterprise Manager.

    -or-
  • 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.

REFERENCES

For additional information about schema changes, visit the following Microsoft Web site:

http://msdn.microsoft.com/library/en-us/replsql/replimpl_22pf.asp

For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

293464 FIX: Merge replication does not log conflicts after successively adding and dropping columns

319691 FIX: Merge subscriptions may reinitialize unexpectedly if incremental schema change fails

Modification Type:MajorLast Reviewed:12/3/2004
Keywords:kbTSQL kberrmsg kbprb KB816755 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.