Problem dropping replication support

I am working on establishing a merge replication process between SQL Server 2005 and SQL Mobile 2005. I started with a new SQL Server 2005 instance and went through the Sample for SQL Mobile merge replication. So far so good.

Today, I tried to drop all support for replication on my current SQL 2005 test instance, so that I can start from a fresh instance and do another. I used Management Studio to drop all publisher and distribution settings and had several errors occur, where some roles were not allowed to be dropped because they had membership in them.

I dropped all the users that I added to the SQL logins and tried again.

Now I am trying to run the following script:

use AdventureWorks
exec sp_replicationdboption @.dbname = N'AdventureWorks', @.optname = N'merge publish', @.value = N'false', @.ignore_distributor = 'true'

-- Dropping the distribution publishers
exec sp_dropdistpublisher @.publisher = N'XP-MIKED-LAPTOP'
GO

-- Dropping the distribution databases
use master
exec sp_dropdistributiondb @.database = N'distribution'
GO

/****** Uninstalling the server XP-MIKED-LAPTOP as a Distributor. Script Date: 1/14/2006 2:16:29 PM ******/

use master
exec sp_dropdistributor @.no_checks = 1, @.ignore_distributor = 1
GO

The error I am getting from the first batch (sp_replicationdboption) is this:

Msg 208, Level 16, State 1, Procedure sp_MSmergepublishdb, Line 103
Invalid object name 'dbo.sysmergesubscriptions'.

To me it looks like all the publication objects have been already removed from AdventureWorks, but in sysdatabases, the category column still says 4 (merge publication). Since I can't just do this anymore:

UPDATE MASTER.DBO.SYSDATABASES
set category = 0
where dbid=8

I just don't know what I can do at this point. I can't even create a new publication in AdventureWorks because it thinks there is a sysmergepublications table in there and fails when there isn't.

try sp_removedbreplication.

|||

Greg Yvkoff wrote:

try sp_removedbreplication.

Yes, that was exactly it. I got to the end of the KB article 324401 and found that stored proc. It worked like a charm.