다음을 통해 공유

Replication: Replication Monitor only shows a red X after upgrading from SQL 2000 SP4 to SQL 2008 SP1

  • 아티클

Problem:

When I upgrade my SQL Server 2000 SP4 to SQL 2008 SP1, I am unable to verify my replication using Replication Monitor as it only shows a red X.

I remove and re-add the publisher but still obtain the same result.

Cause / Workaround:

The issue at hand is due to the fact that the table sp_replicationmonitordata was created to not allow nulls.

Workaround would be:

1. Drop/Create the table to allow the columns to allow nulls – beforehand you should generate the script for this table.
2. Execute the upgrade stored procedure: sp_vupgrade_replication –> this should be the best option as this is the stored procedure that was created to upgrade replication

Comments

  • Anonymous
    October 17, 2009
    This is the solution which worked in my case for a SQL Server 2008 install which have been upgraded from SQL server 2000 SP4 (Only one tested) with merge replication where same server was both publisher and distributor. After the upgrade the replication monitor behaves strangely. My symptoms were: not displaying publications on publisher, not showing subscribers and replication monitor disconnecting almost instantly on connect the only thing that was visible were my snapshot agents. Cumulative update package 3 for SQL Server 2008 Service Pack 1 http://support.microsoft.com/kb/971491 reports this as fixed but it did not work in my circumstance. Above workaround made replication monitor functional. The table name to set columns to accept NULLs is dbo.distribution.MSreplication_monitordata SQL Trace identified the problem  in my case with: Cannot insert the value NULL into column 'isagentrunningnow' setting just this column to allow nulls gave the error in the next column 'last_distsync': As in work around ALL columns should be set to allow nulls.