MSSQLServer无法修改表 因为当前正在复制

　　今天再使用mssqlserver2000的复制功能的时候，在成功发布后，尽然无法修改这个被发布的数据库中表的结构了，提示“无法修改表 .....因为当前正在复制”的错误。

　　后面只好删除了发布再修改数据库的表结构，不提示“无法修改表 因为当前正在复制”错误了，但是这个也太麻烦了吧。。：－（

微软的解释

Troubleshooting (SQL Server 2000)
Error 4929
Error 4929

  Topic last updated -- January 2004
Severity Level 16
Message Text

Cannot alter the %S_MSG '%.*ls' because it is being published 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 alter 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 and when a snapshot is being applied to reinitialize a Subscriber.

Note  Most schema changes on replicated objects are disallowed, 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
          o How to delete publications and articles (Enterprise Manager)

          o How to delete a push subscription (Enterprise Manager)

          o How to delete a pull or anonymous subscription (Enterprise Manager)
    * Transact-SQL
          o How to Delete Publications and Articles (Transact-SQL)

          o How to Delete a Push Subscription (Transact-SQL)

          o How to Delete a Pull Subscription (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
          o How to disable publishing and distribution (Enterprise Manager)
    * Transact-SQL
          o How To Disable Publishing and Distribution (Transact-SQL)

Error occurs when the snapshot is applied

When applying a snapshot to a Subscriber, you have four options if an object with the same name exists at the Subscriber:

    * Keep the existing table unchanged.

    * Drop the existing table and recreate it.

    * Delete data in the existing table that matches the row filter statement.

    * Delete all data in the existing table.

If you are receiving error message 4929 during snapshot application at the Subscriber, ensure that you choose to keep the existing table unchanged:

   1. In SQL Server Enterprise Manager, right-click the publication and click Properties.

   2. Click the Articles tab, and click Article Defaults.

   3. Click the Snapshot tab on the Default Table Article Properties dialog box.

   4. Select the option Keep the existing table unchanged and click OK.

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 table information:

    * 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

   1. 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'

   2. 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

   3. Run the following code:

      sp_configure 'allow updates', 0
      go
      reconfigure with override
      go

加支付宝好友偷能量挖...

原创文章，转载请注明出处：MSSQLServer无法修改表 因为当前正在复制