Cannot create publication after SP1 upgrade

After upgrading SQL Server 2005 with the recent service pack, I am unable to create a publication. When I right-click on [INSTANCE]/Replication/Local Publications and select "New Publication...," the mouse cursor turns into an hourglass, but the wizard never shows up, nor is any error displayed.

There is a logged error that's been showing up that may or may not be related. The error message is "Replication-(null): agent (null) scheduled for retry. Could not clean up the distribution history tables."

Any ideas on how we can restore replication functionality on our SQL Server instance?

Can you run profiler before invoking New Publication wizard to trace which statement is causing the problem? What kind of publications do you have before upgrade to SP1? Is distributor on the same machine as publisher?

Thanks,

Zhiqiang Feng

This posting is provided "AS IS" with no warranties, and confers no rights.

|||I ran the profiler, and I'm getting some strange output. Apparently, the command "select * from sysdatabases where name=N'distribution'" is being run over and over again. Strangely enough, there is a row returned from that command that points to where our distribution database was located, but the database isn't shown under the system databases in Management Studio.

Before upgrading to SP1, we were using merge publications with the distributor on the same machine as the publisher.

|||

Can you send sp1 upgrade log files and profiler trace to me? I will send you a separate email.

Thanks,

Zhiqiang Feng

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

From the sp1 upgrade log file, looks like you have run SP1 upgrade twice, the first time it runs, SQLDEV01 machine is treated as a distributor and distribution database is upgrade successfully(from what the log file says). The 2nd time the sp1 upgrade runs, it didn't treat this machine as distributor and didn't upgrade distribution database (also you mentioned distribution database is no longer marked as system database, which is suspicious, we need to figure out why). Did you notice any error during sp1 upgrade? Why you run sp1 upgrade twice? Did you drop distributor or change replication settings after first sp1 upgrade?

Please also email a profiler trace for the New publication wizard so we can further diagnose the issue. When using profiler, make sure select following options (and unselect any options not mentioned below):

Error and Warnings --> ErrorLog, Exceptions, User Error Message

Stored Procedures --> RPC Starting, RPC Completed, SP Starting, SP Completed, SP Stmt Starting

TSQL --> SQL: Batch Starting, SQL: Batch Completed, SQL: StmtStarting

Thanks,

Zhiqiang Feng

This posting is provided "AS IS" with no warranties, and confers no rights.

|||It's finally working! I suspect that all the problems stemmed from there being an inconsistency in the sysdatabases table. I ran "DROP DATABASE distributor" under the master database, and afterward, I was able to set up the distributor, publishers, and subscribers without any problems.

Thank you, Zhiqiang Feng, for your email assistance.