PRB: Upgrading Replication Settings May Fail When Inserting Rows into Msdistribution_agents

• Microsoft SQL Server version 7.0

SYMPTOMS

When upgrading SQL Server from SQL 6.5 to SQL 7.0 with replication settings, the upgrade process may fail at the "import replication settings" step with the following error:

Microsoft SQL-DMO (ODBC SQLState: 23000) returned error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column 'name', table 'distribution.dbo.MSdistribution_agents'; column does not allow nulls. INSERT fails.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.

CAUSE

If there are no rows in systasks that pertain to the distribution task, the insert into distribution.dbo.MSdistribution_agents fails.

Non existence of rows corresponding to each distribution task in msdb.dbo.systasks may be due to the following reasons:

1. Running the upgrade wizard for the first time with replication settings but without SQL Executive settings. This method does not upgrade the MSDB database.



2. When you run the upgrade wizard once with replication settings and SQL Executive settings selected, but after the SQL 7.0 upgrade, replication is completely removed on SQL Server 7.0 and the upgrade wizard runs a second time with replication settings. When replication is dropped from SQL Server 7.0, all the distribution tasks entries that were imported to the MSDB database on SQL Server 7.0 are removed and when the upgrade wizard runs a second time with replication settings, the MSDB systasks are not imported again. This results in an empty systasks table and hence failure to insert rows into distribution.dbo.MSdistribution_agents.

WORKAROUND

It is always recommended that sqlexecutive settings, replication settings and user databases be upgraded at the same time. Do not upgrade replication settings without choosing to upgrade SQL Executive settings.

If replication on SQL Server 7.0 has to be dropped after upgrading and the upgrade wizard has to run again to upgrade replication settings for some reason, you must use one of the following workarounds:

• Reinstall SQL Server 7.0 before running the upgrade wizard again.
  
  -or-



• Restore the master database from a backup that was created before the upgrade wizard initially was run.

MORE INFORMATION

When replication settings are upgraded, the distribution.dbo.msdistribution_agents table is populated with entries produced as a result of a join between distribution.dbo.MSpublisher_databases, msdb.dbo.systasks_view, and distribution.dbo.repl_upd_msjob_subscriptions. The MSpublisher_databases and repl_upd_msjob_subscriptions tables are created during the upgrade process for sake of replication. The msdb.dbo.systasks_view is a view on msdb.dbo.systasks. If the MSDB database was upgraded along with or prior to the replication settings being upgraded, msdb.dbo.systasks_view would return rows corresponding to each distribution task.

Additional query words:

Keywords :
Version : winnt:7.0
Platform : winnt
Issue type : kbprb