Microsoft KB Archive/326352

= PRB: You receive error 3724 when you drop a non-replicated object =

Article ID: 326352

Article Last Modified on 10/11/2004

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q326352



SYMPTOMS
When you try to drop or to rename an object (a table, a view, or a stored procedure) that is not replicated, you may receive an error message that is similar to one of the following:

3724: Cannot %S_MSG the %S_MSG '%.*ls' because it is being used for replication.

15051: Cannot rename the table because it is published for replication.



WORKAROUND
IMPORTANT: The 3724 message may occur legitimately when an object is marked for replication. Do not use the following workaround if the object is being used for replication.

Steps to Take Before You Work Around This Problem
  Verify that no publications or subscriptions use the object as an article. You can check the sysarticles and sysmergearticles tables that are in your database to verify this.

For example, run the following queries against your database that contains the object that you want to drop: select * from database_name..sysarticles where name = 'object_name' select * from database_name..sysmergearticles where name = 'object_name' If you see the object name in either query, do not use the following workaround. If you do not want the to use the database for replication, remove replication. Whenever possible, remove replication by using SQL Server Enterprise Manager or by using scripts. For more information, see the &quot;Disabling Publishing and Distribution&quot; topic in SQL Server Books Online. If you have no other options, try the workaround.   Make sure that you did not enable this database for replication by using the sp_helpreplicationdboption system stored procedure. Do not enable the database for replication. If it is enabled for replication, run the sp_removedbreplication stored procedure to disable replication.

IMPORTANT: Running sp_removedbreplication on a database removes all replication objects from the database. Therefore, all publications and subscriptions in the database are removed. Only members of the sysadmin fixed server role can run the sp_removedbreplication stored procedure. For more information about the sp_removedbreplication stored procedure, see the &quot;sp_removedbreplication, Transact-SQL Reference&quot; topic in SQL Server Books Online.

To run the sp_removedbreplication stored procedure, use the following code in which you replace  with the name of your database: sp_removedbreplication 'dbname'  Make sure to have a contingency plan, such as a database backup. Microsoft recommends that you take a full database backup of the database before you use the workaround. For more information about the backup command, see the &quot;BACKUP, Transact-SQL Reference&quot; topic in SQL Server Books Online.

Steps to Work Around This Behavior
After you make sure that you have considered all other potential options, mark all objects in the database as not used by replication:

NOTES:
 * 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.

  Run the following code. Replace  with the name of the object that you must modify: 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 commit transaction go   Run the following code: sp_configure 'allow updates', 0 go reconfigure with override go </li></ol>

<div class="moreinformation_section">

MORE INFORMATION
For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

243377 FIX: Merge system tables not dropped when restoring merge-published DB over non-published DB across servers

272115 PRB: Stored procedure still marked for replication may cause stack dumps

For additional information, see the following topics in SQL Server 2000 Books Online:
 * &quot;Backing Up and Restoring Replication Databases&quot;
 * &quot;Strategies for Backing Up and Restoring Merge Replication&quot;
 * &quot;Strategies for Backing Up and Restoring Transactional Replication&quot;
 * &quot;Strategies for Backing Up and Restoring Snapshot Replication&quot;
 * &quot;sp_restoredbreplication&quot;
 * &quot;sp_vupgrade_replication&quot;
 * &quot;System Tables&quot;

Additional query words: repl dump load backup restore rename change table stored procedure view storproc

Keywords: kbnofix kbprb KB326352

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.