Microsoft KB Archive/835860

= How to remove SQL Server replication attributes from all replicated objects in a restored Systems Management Server 2003 site database =

Article ID: 835860

Article Last Modified on 10/27/2006

-

APPLIES TO


 * Microsoft Systems Management Server 2003

-





INTRODUCTION
This article describes how to remove Microsoft SQL Server replication attributes from all replicated objects in a restored Microsoft Systems Management Server (SMS) 2003 site database.

You must remove SQL Server replication attributes before you do the following:
 * Upgrade your previously restored SMS site database.
 * Test an SMS site database that has been restored to a different server by using the SMS Setup program with the /testdbupgrade switch.



How to remove SQL Server replication attributes from all replicated objects in a restored SMS site database
We do 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 remove the SQL Server replication attributes, follow these steps.

Note The following procedure depends on SQL Server system tables. The structure of these tables may vary with different SQL Server versions.  Click Start, point to Programs, click Microsoft SQL Server, and then click Enterprise Manager. In SQL Server Enterprise Manager, click SQL Query Analyzer on the Tools menu. Above the Query window, click the Database list, and then click the restored SMS database.  In the Query window in the right pane, copy and paste the following code. sp_configure 'allow updates', 1 go reconfigure with override go begin transaction update sysobjects set replinfo = '0' where replinfo >'0'  Click the Parse Query button to look for errors in the code. Click the Execute Query button to run the query on the restored database.  Verify that all objects in the restored database's sysobjects table have the replinfo attribute set to 0 (zero), and then verify that only one row was affected. If the intended row in the sysobjects table was updated, commit or roll back the transaction by using one of the following commands. commit transaction go rollback transaction go </li>  Run the following code to set the allow updates attribute back to 0 (zero). sp_configure 'allow updates', 0 go reconfigure with override go </li></ol>

You can re-create the replication objects after you upgrade the SMS site database or after you run the the SMS Setup program with the /testdbupgrade switch to test the database.

<div class="references_section">