changed subscriber to publisher, now double constraints

[jmille34]

I had sql 2000 merge replication going between a publisher and a subscriber. The publisher failed, so I redirected traffic to the subscriber. That was working fine, so I decided to reverse it. I deleted the replication and started over with the subscriber as the new publisher. The problem is that now there are double constraints on my identity fields which is causing sql to fail to allocate the next batch of id's, so I'm getting "the identity range is full" but even running Sp_adjustpublisheridentityrange won't force it to allocate the next block. I have tried deleting both constraints, and that does seem to work to at least get the applications running again, but I doubt this is the correct thing to do. If it would be better to only delete one, then how do I tell which one to delete? Would I be better off stripping out all the replication, including the tables and rowguid fields and constraints and everything and really really start over? I thought that's what I as doing before, but it clearly had a lot left over that it wasn't smart enough to get rid of and I didn't notice.

 

[jmille34]

I turned off replication on one database, but it left a ton of junk in the database. Is there a script to get rid of all this garbage? The tables conflict*, msmerge*, identity constraints, and anything else that goes with it? I want to set up replication from scratch on this database, but I'm having a tough time getting it back to scratch.

 

[jmille34]

Ok, I needed to refresh, and the tables are gone. But it didn't get rid of the constraints. Can I simply delete them from the sysobjects table?

 

[jmille34]

I found this script that seems to undo everything important..it's kind of long but I'm going to try to paste it in here.. I have 12 replicated databases that all had basically the same problem from changing the subscriber to the publisher and having wacky constraints.. so I turned off all replication and then ran this script against all 12 databases and then set up replication from scratch.

USE [DBNAME]
GO


-- Drop all replication triggers from the database
PRINT 'Drop all replication triggers from the database'
SELECT trigs.name AS TriggerName,
trigs.id AS TriggerID,
tables.name AS TableName
INTO #Triggers
FROM sysobjects trigs
INNER JOIN sysobjects tables
ON trigs.parent_obj=tables.id
where trigs.category=2 and trigs.xtype='TR'

DECLARE @TriggerName varchar(100), @TriggerID INT, @TableName varchar(100)

DECLARE cur CURSOR for SELECT * FROM #Triggers
OPEN cur
FETCH NEXT FROM cur INTO @TriggerName, @TriggerID, @TableName
WHILE @@FETCH_STATUS=0
BEGIN
EXECUTE ('DROP TRIGGER ' + @TriggerName)
FETCH NEXT FROM cur INTO @TriggerName, @TriggerID, @TableName
END
CLOSE cur
DEALLOCATE cur
GO

-- Drop all replication constraints from the database
PRINT 'Drop all replication constraints from the database'
DECLARE @ConstName varchar(100), @ConstID INT, @TableName varchar(100)

SELECT CONST.name AS ConstName,
CONST.id AS ConstID,
tables.name AS TableName
INTO #Constraints
FROM sysobjects CONST
INNER JOIN sysobjects tables
ON CONST.parent_obj=tables.id
where CONST.xtype='C'

DECLARE cur CURSOR for SELECT * FROM #Constraints
OPEN cur
FETCH NEXT FROM cur INTO @ConstName, @ConstID, @TableName
WHILE @@FETCH_STATUS=0
BEGIN
EXECUTE ('ALTER Table ' + @TableName + ' DROP CONSTRAINT ' + @ConstName)
FETCH NEXT FROM cur INTO @ConstName, @ConstID, @TableName
END
CLOSE cur
DEALLOCATE cur

GO

-- Drop all replication User tables
PRINT 'Drop all replication User tables'
DECLARE @TableName varchar(100), @TableID INT

SELECT Tables.name AS ConstName,
Tables.id AS ConstID
INTO #Tables
FROM sysobjects Tables
where Tables.xtype='U' AND Status < 0 AND category=2050

DECLARE cur CURSOR for SELECT * FROM #Tables
OPEN cur
FETCH NEXT FROM cur INTO @TableName, @TableID
WHILE @@FETCH_STATUS=0
BEGIN
EXECUTE ('DROP Table ' + @TableName)
FETCH NEXT FROM cur INTO @TableName, @TableID
END
CLOSE cur
DEALLOCATE cur

GO

-- Drop all replication User procedures
PRINT 'Drop all replication User procedures'
DECLARE @ProcName varchar(100), @ProcID INT

SELECT Procs.name AS ConstName,
Procs.id AS ConstID
INTO #Procedures
FROM sysobjects Procs
where procs.xtype='P' AND Status < 0

DECLARE cur CURSOR for SELECT * FROM #Procedures
OPEN cur
FETCH NEXT FROM cur INTO @ProcName, @ProcID
WHILE @@FETCH_STATUS=0
BEGIN
EXECUTE ('DROP Procedure ' + @ProcName)
FETCH NEXT FROM cur INTO @ProcName, @ProcID
END
CLOSE cur
DEALLOCATE cur

GO

-- Drop all replication User Views
PRINT 'Drop all replication User Views'
DECLARE @ViewName varchar(100), @ViewID INT

SELECT MyViews.name AS ConstName,
MyViews.id AS ConstID
INTO #Views
FROM sysobjects MyViews
where MyViews.xtype='V' AND Status < 0 AND Name NOT LIKE 'sys%'

DECLARE cur CURSOR for SELECT * FROM #Views
OPEN cur
FETCH NEXT FROM cur INTO @ViewName, @ViewID
WHILE @@FETCH_STATUS=0
BEGIN
EXECUTE ('DROP View ' + @ViewName)
FETCH NEXT FROM cur INTO @ViewName, @ViewID
END
CLOSE cur
DEALLOCATE cur

GO


-- Drop all replication rowguids, defaults and indexes
PRINT 'Drop all replication rowguids, defaults and indexes'
SELECT tables.name as TableName,
defaults.name AS DefaultName,
Indexes.IndexName,
cols.name AS ColumnName
INTO #Defaults
FROM sysobjects defaults
INNER JOIN syscolumns cols
ON defaults.ID=cols.cdefault
INNER JOIN sysobjects tables
ON tables.id=cols.id
INNER JOIN
(select sysindexes.name AS IndexName,
Tables.Name AS TableName,
Tables.id as TableID,
cols.Name AS ColumnName
from sysindexes
INNER JOIN sysobjects Tables
ON sysindexes.id=tables.id
INNER JOIN sysindexkeys k
on sysindexes.id=k.id
AND sysindexes.indid=k.indid
INNER JOIN syscolumns cols
ON k.id=cols.id
AND k.colid=cols.colid
where cols.name='rowguid') Indexes
ON Indexes.TableID=tables.id
where cols.name='rowguid'

DECLARE @DefaultName varchar(100), @IndexName varchar(100), @TableName varchar(100), @ColName varchar(50)

DECLARE cur CURSOR for SELECT * FROM #Defaults
OPEN cur
FETCH NEXT FROM cur INTO @TableName, @DefaultName, @IndexName, @ColName
WHILE @@FETCH_STATUS=0
BEGIN
EXECUTE ('ALTER TABLE ' + @TableName + ' DROP CONSTRAINT ' + @DefaultName)
EXECUTE ('DROP INDEX ' + @TableName + '.' + @IndexName)
EXECUTE ('ALTER TABLE ' + @TableName + ' DROP COLUMN rowguid')
FETCH NEXT FROM cur INTO @TableName, @DefaultName, @IndexName, @ColName
END
CLOSE cur
DEALLOCATE cur
GO