Error 3724
Error 3724
Topic last updated -- January 2004
Severity Level 16
Message Text
Cannot %S_MSG the %S_MSG '%.*ls' because it is being used for replication.
Explanation
When objects in a database are replicated, they are marked as replicated in the system tables sysarticles (for snapshot and transactional publications) and sysmergearticles (for merge publications). If you try to alter or drop a replicated object, you receive an error message similar to:
Cannot drop the table 'Employees' because it is being used for replication.This error message can also occur incorrectly in some circumstances when an object is not replicated.
Note Most schema changes on replicated objects are not allowed, but you can add or drop columns in a replicated table. For more information, see Schema Changes on Publication Databases.
Action
This error typically occurs for objects that are replicated, but in some cases can occur incorrectly for objects that are not replicated.
Error occurs for objects that are replicated
For objects that are replicated, remove the object from the publication before attempting to alter or drop it. If the publication has subscriptions, you must delete all subscriptions before removing the object from the publication. See the following topics for more information:
- Enterprise Manager
- Transact-SQL
If removing the article does not solve the problem, you might need to remove replication from the server completely. See the following topics for more information:
- Enterprise Manager
- Transact-SQL
Error occurs for objects that are not replicated
For objects that are not replicated, you must ensure that they are not marked as replicated in the system tables. If they are marked as replicated, the system tables must be updated. There are two approaches to updating the system tables:
- The first approach (recommended) is calling the stored procedure sp_removedbreplication on the database that contains the object:
sp_removedbreplication 'dbname' - The second approach is to update the system tables directly.
Important You should only use this approach after other options have been exhausted. Ensure you have backed up the database first.
The following procedure is dependent on SQL Server system tables. The structure of these tables may vary in different SQL Server versions. Microsoft does not recommend that you select directly from the system tables.
In most cases, Microsoft does not recommend that you (or any user) change system tables directly. For example, do not try to modify system tables by using DELETE, UPDATE, or INSERT statements, or by using user-defined triggers.
To update system tables
- Execute the following code in Query Analyzer. Replace object_name with the name of the object that is marked for replication:
sp_configure 'allow updates', 1 go reconfigure with override go begin transaction update sysobjects set replinfo = 0 where name = 'object_name' - Verify that only one row was affected. If the intended row in the sysobjects table was updated, commit the transaction, or roll back the transaction by using the following appropriate command:
rollback transaction go -- or commit transaction go - Run the following code:
sp_configure 'allow updates', 0 go reconfigure with override go