Cannot add publication after applying service pack 1

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jacob Page - 24 May 2006 21:23 GMT

After applying service pack 1 to SQL Server 2005, I can no longer set up
publications. Here's what I'm attempting:

Under the Object Explorer of SQL Server Management Studio, I'm
right-clicking on [INSTANCE]/Replication/Local Publications and selecting
"New Publication..." After clicking on that menu item, the mouse cursor
turns to an hourglass, but nothing happens afterward. For a minute or so
afterward, if I attempt any other actions in Management Studio, I'm given a
message that SQL Server is currently busy.

There is an entry in the error log that occurs in the same timeframe as my
attempted action. It reads, "Replication-(null): agent (null) scheduled for
retry. Could not clean up the distribution history tables."

Reply to this Message

Hilary Cotter - 25 May 2006 02:52 GMT

Its unclear to me whether the problem is with management studio or
replication itself.

Can you try to create a publication using the stored procedures. try this
for example.

create database sp1replicationtest

go

use sp1replicationtest

go

sp_replicationdboption 'sp1replicationtest','publish','true'

go

sp_addpublication 'test',@status='active'

go

sp_addpublication_snapshot 'test'

go

create table test(pk int not null primary key, charcol char)

go

sp_addarticle 'test','test','test'

go

Signature

Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

> After applying service pack 1 to SQL Server 2005, I can no longer set up
> publications. Here's what I'm attempting:
[quoted text clipped - 11 lines]
> for
> retry. Could not clean up the distribution history tables."

Reply to this Message

Jacob Page - 25 May 2006 16:56 GMT

When executing sp_replicationdboption 'database_name','merge publish','true',
I'm given the following response:

Msg 20028, Level 16, State 1, Procedure sp_MSmergepublishdb, Line 60
The Distributor has not been installed correctly. Could not enable database
for publishing.
The replication option 'merge publish' of database 'database_name' has been
set to false.

> Its unclear to me whether the problem is with management studio or
> replication itself.
[quoted text clipped - 45 lines]
> > for
> > retry. Could not clean up the distribution history tables."

Reply to this Message

Jacob Page - 25 May 2006 17:29 GMT

By the way, I also tried the "Configure Distribution" context menu item, and
nothing happens. There is, however, something strange going on according to
SQL Server Profiler. The command "select * from sysdatabases where
name=N'distribution'" is being run constantly. There is a row in that table
that points to the distribution database, but the distribution database
doesn't show up in Managment Studio.

> Its unclear to me whether the problem is with management studio or
> replication itself.
[quoted text clipped - 45 lines]
> > for
> > retry. Could not clean up the distribution history tables."

Reply to this Message

Jacob Page - 27 May 2006 02:31 GMT

I found the solution to my problem. After running DROP DATABASE distributor
under the master database, I was able to set up my distributor, publishers,
and subscriptions just fine.

Reply to this Message