Cleaning replication manually safely....

[faustino Dina]

Hi,

Which are the tables, stored procedures and triggers involved in the
replication (merge replication) SQL Server 2000 SP3?
How to check all that for inconsistencies?
How to clean them manually safely?

I have a problem of inconsistency in my replication metadata. The large
history is the following:
After backup/restoring the publication database (the distributor is in
another PC) I realized that the publication was corrupted. It was not listed
on the distribution server (Replication Monitor/Agents/Snapshot Agents). The
Enterprise manager listed correctly the name under
Replications/Publications, but when I tryed to delete the publication I
received the error that the publication doesn't exist. When tryed to watch
the properties dialog box for the subscription, it appears. I could see the
General and Articles tabs but when tryed with a third, it reported an error
and broke.
Then I tryed to modify a table that was part of that corrupted publication.
When saving the changes I received the error: "Can not modify the table
'cause it is part of a publication".
Then I tryed to remove replication from the server. I disabled replication
by right clicking on the server/Replication folder. I finally got the
subscription to disappear from displaying on the Publications folder. But
when trying to modify the table, I received the same error: it was part of a
publication....
So what to do?
Any hint is welcomed

Thanks in advance
Faustino

[Hilary Cotter]

There are several factors here.

If I restore a published database or a subscriber to a SQL
Server which does not have replication on it - most of the
metadata (replication tables,
sysmergeextendedarticlesview,sysextendedarticlesview) are
gone. However some of the bit mask values are retained in
the system tables - replinfo in sysobjects.

What will remain are views, and sps, guids, replinfo
values.

To whack these do this:

UPDATE sysobjects set replinfo=0
GO

DECLARE @name nvarchar(129)
DECLARE list_views CURSOR FOR
SELECT name FROM sysobjects WHERE type='V' and (name
like 'syncobj_%' or name like 'ctsv_%' or name
like 'tsvw_%')

OPEN list_views

FETCH NEXT FROM list_views INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'dropping View ' +@name
select @name='drop View ' + @name
EXEC sp_executesql @name
FETCH NEXT FROM list_views INTO @name
END

CLOSE list_views
DEALLOCATE list_views

GO

DECLARE @name nvarchar(129)
DECLARE list_procs CURSOR FOR
SELECT name FROM sysobjects WHERE type='p' and (name
like 'sp_ins_%' or name like 'sp_MSdel_%' or name
like 'sp_MSins_%'or name like 'sp_MSupd_%' or name
like 'sp_sel_%' or name like 'sp_upd_%')

OPEN list_procs

FETCH NEXT FROM list_procs INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'dropping procs ' +@name
select @name='drop procedure ' + @name
EXEC sp_executesql @name
FETCH NEXT FROM list_procs INTO @name
END

CLOSE list_procs
DEALLOCATE list_procs

GO

DECLARE @name nvarchar(129)
DECLARE list_conflict_tables CURSOR FOR
SELECT name From sysobjects WHERE type='u' and name
like '_onflict%'

OPEN list_conflict_tables

FETCH NEXT FROM list_conflict_tables INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'dropping conflict_tables ' +@name
select @name='drop Table ' + @name
EXEC sp_executesql @name
FETCH NEXT FROM list_conflict_tables INTO @name
END

CLOSE list_conflict_tables
DEALLOCATE list_conflict_tables

GO

Declare @name nvarchar(129), @constraint nvarchar(129)
DECLARE list_rowguid_constraints CURSOR FOR
select object_name(sysobjects.parent_obj), sysobjects.name
from sysobjects, syscolumns where sysobjects.type ='d'
and syscolumns.id=sysobjects.parent_obj
and syscolumns.name='rowguid'

OPEN list_rowguid_constraints

FETCH NEXT FROM list_rowguid_constraints INTO @name,
@constraint
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'dropping rowguid constraints ' +@name
select @name='ALTER TABLE ' + rtrim(@name ) + '
DROP CONSTRAINT ' +@constraint
EXEC sp_executesql @name
FETCH NEXT FROM list_rowguid_constraints INTO
@name, @constraint
END

CLOSE list_rowguid_constraints
DEALLOCATE list_rowguid_constraints

GO

Declare @name nvarchar(129), @constraint nvarchar(129)
DECLARE list_rowguid_indexes CURSOR FOR
select object_name(id), name from sysindexes where name
like 'index%'

OPEN list_rowguid_indexes

FETCH NEXT FROM list_rowguid_indexes INTO @name,
@constraint
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'dropping rowguid indexes ' +@name
select @name='drop index ' + rtrim(@name ) + '.'
+@constraint
EXEC sp_executesql @name
FETCH NEXT FROM list_rowguid_indexes INTO @name,
@constraint
END

CLOSE list_rowguid_indexes
DEALLOCATE list_rowguid_indexes

GO
Declare @name nvarchar(129)
DECLARE list_rowguid_columns CURSOR FOR
select object_name(syscolumns.id) from syscolumns,
sysobjects where syscolumns.name like 'rowguid'
and sysobjects.id=syscolumns.id
and sysobjects.type='u' order by 1

OPEN list_rowguid_columns

FETCH NEXT FROM list_rowguid_columns INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'dropping rowguid columns ' +@name
select @name='Alter Table ' + rtrim(@name ) + '
drop column rowguid'
print @name
EXEC sp_executesql @name
FETCH NEXT FROM list_rowguid_columns INTO @name
END

CLOSE list_rowguid_columns
DEALLOCATE list_rowguid_columns

DELETE FROM sysmergepublications
GO
DELETE FROM sysmergesubscriptions
GO
DELETE FROM syssubscriptions
GO
DELETE FROM sysarticleupdates
GO
DELETE FROM systranschemas
GO
DELETE FROM sysmergearticles
GO
DELETE FROM sysmergeschemaarticles
GO
DELETE FROM sysmergesubscriptions
GO
DELETE FROM sysarticles
GO
DELETE FROM sysschemaarticles
GO
DELETE FROM syspublications
GO
DELETE FROM sysmergeschemachange
GO
DELETE FROM sysmergesubsetfilters
GO
DELETE FROM MSdynamicsnapshotjobs
GO
DELETE FROM MSdynamicsnapshotviews
GO
DELETE FROM MSmerge_altsyncpartners
GO
DELETE FROM MSmerge_contents
GO
DELETE FROM MSmerge_delete_conflicts
GO
DELETE FROM MSmerge_errorlineage
GO
DELETE FROM MSmerge_genhistory
GO
DELETE FROM MSmerge_replinfo
GO
DELETE FROM MSmerge_tombstone
GO
DELETE FROM MSpub_identity_range
GO
DELETE FROM MSrepl_identity_range
GO
DELETE FROM MSreplication_subscriptions
GO
DELETE FROM MSsubscription_agents
GO

If I restore a publisher or a subscriber to the same
server and database and I do or do not use the
keep_replication switch with the restore, you will be able
to remove replication using the GUI. You may lose
transactions that were applied since the backup was done.

If I restore a published database or a subscriber to a SQL
Server that was involved in replication but I restore it
to a different database or server, you will not have much
success dropping the replication object using the GUI.

In this case try the below commands:

DECLARE @name varchar(129)
DECLARE list_pubs CURSOR FOR
SELECT name FROM syspublications

OPEN list_pubs

FETCH NEXT FROM list_pubs INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'dropping publication ' +@name
EXEC sp_dropsubscription @publication=@name,
@article='all', @subscriber ='all'
EXEC sp_droppublication @name
FETCH NEXT FROM list_pubs INTO @name
END

CLOSE list_pubs
DEALLOCATE list_pubs
GO

DECLARE @name varchar(129)
DECLARE list_replicated_tables CURSOR FOR
SELECT name FROM sysobjects WHERE replinfo <>0
UNION
SELECT name FROM sysmergearticles

OPEN list_replicated_tables

FETCH NEXT FROM list_replicated_tables INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'unmarking replicated table ' +@name
--select @name='drop Table ' + @name
EXEC sp_msunmarkreplinfo @name
FETCH NEXT FROM list_replicated_tables INTO @name
END

CLOSE list_replicated_tables
DEALLOCATE list_replicated_tables

GO

UPDATE syscolumns set colstat = colstat & ~4096 WHERE
colstat &4096 <>0
GO
UPDATE sysobjects set replinfo=0
GO

DECLARE @name nvarchar(129)
DECLARE list_views CURSOR FOR
SELECT name FROM sysobjects WHERE type='V' and (name
like 'syncobj_%' or name like 'ctsv_%' or name
like 'tsvw_%')

OPEN list_views

FETCH NEXT FROM list_views INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'dropping View ' +@name
select @name='drop View ' + @name
EXEC sp_executesql @name
FETCH NEXT FROM list_views INTO @name
END

CLOSE list_views
DEALLOCATE list_views

GO

DECLARE @name nvarchar(129)
DECLARE list_procs CURSOR FOR
SELECT name FROM sysobjects WHERE type='p' and (name
like 'sp_ins_%' or name like 'sp_MSdel_%' or name
like 'sp_MSins_%'or name like 'sp_MSupd_%' or name
like 'sp_sel_%' or name like 'sp_upd_%')

OPEN list_procs

FETCH NEXT FROM list_procs INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'dropping procs ' +@name
select @name='drop procedure ' + @name
EXEC sp_executesql @name
FETCH NEXT FROM list_procs INTO @name
END

CLOSE list_procs
DEALLOCATE list_procs

GO

DECLARE @name nvarchar(129)
DECLARE list_conflict_tables CURSOR FOR
SELECT name From sysobjects WHERE type='u' and name
like '_onflict%'

OPEN list_conflict_tables

FETCH NEXT FROM list_conflict_tables INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'dropping conflict_tables ' +@name
select @name='drop Table ' + @name
EXEC sp_executesql @name
FETCH NEXT FROM list_conflict_tables INTO @name
END

CLOSE list_conflict_tables
DEALLOCATE list_conflict_tables

GO

UPDATE syscolumns set colstat=2 WHERE name='rowguid'

GO

Declare @name nvarchar(129), @constraint nvarchar(129)
DECLARE list_rowguid_constraints CURSOR FOR
select object_name(sysobjects.parent_obj), sysobjects.name
from sysobjects, syscolumns where sysobjects.type ='d'
and syscolumns.id=sysobjects.parent_obj
and syscolumns.name='rowguid'

OPEN list_rowguid_constraints

FETCH NEXT FROM list_rowguid_constraints INTO @name,
@constraint
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'dropping rowguid constraints ' +@name
select @name='ALTER TABLE ' + rtrim(@name ) + '
DROP CONSTRAINT ' +@constraint
EXEC sp_executesql @name
FETCH NEXT FROM list_rowguid_constraints INTO
@name, @constraint
END

CLOSE list_rowguid_constraints
DEALLOCATE list_rowguid_constraints

GO

Declare @name nvarchar(129), @constraint nvarchar(129)
DECLARE list_rowguid_indexes CURSOR FOR
select object_name(id), name from sysindexes where name
like 'index%'

OPEN list_rowguid_indexes

FETCH NEXT FROM list_rowguid_indexes INTO @name,
@constraint
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'dropping rowguid indexes ' +@name
select @name='drop index ' + rtrim(@name ) + '.'
+@constraint
EXEC sp_executesql @name
FETCH NEXT FROM list_rowguid_indexes INTO @name,
@constraint
END

CLOSE list_rowguid_indexes
DEALLOCATE list_rowguid_indexes

GO
Declare @name nvarchar(129)
DECLARE list_rowguid_columns CURSOR FOR
select object_name(syscolumns.id) from syscolumns,
sysobjects where syscolumns.name like 'rowguid' and
object_Name(sysobjects.id) not like 'msmerge%'
and sysobjects.id=syscolumns.id
and sysobjects.type='u' order by 1

OPEN list_rowguid_columns

FETCH NEXT FROM list_rowguid_columns INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'dropping rowguid columns ' +@name
select @name='Alter Table ' + rtrim(@name ) + '
drop column rowguid'
print @name
EXEC sp_executesql @name
FETCH NEXT FROM list_rowguid_columns INTO @name
END

CLOSE list_rowguid_columns
DEALLOCATE list_rowguid_columns

DELETE FROM sysmergepublications
GO
DELETE FROM sysmergesubscriptions
GO
DELETE FROM syssubscriptions
GO
DELETE FROM sysarticleupdates
GO
DELETE FROM systranschemas
GO
DELETE FROM sysmergearticles
GO
DELETE FROM sysmergeschemaarticles
GO
DELETE FROM sysmergesubscriptions
GO
DELETE FROM sysarticles
GO
DELETE FROM sysschemaarticles
GO
DELETE FROM syspublications
GO
DELETE FROM sysmergeschemachange
GO
DELETE FROM sysmergesubsetfilters
GO
DELETE FROM MSdynamicsnapshotjobs
GO
DELETE FROM MSdynamicsnapshotviews
GO
DELETE FROM MSmerge_altsyncpartners
GO
DELETE FROM MSmerge_contents
GO
DELETE FROM MSmerge_delete_conflicts
GO
DELETE FROM MSmerge_errorlineage
GO
DELETE FROM MSmerge_genhistory
GO
DELETE FROM MSmerge_replinfo
GO
DELETE FROM MSmerge_tombstone
GO
DELETE FROM MSpub_identity_range
GO
DELETE FROM MSrepl_identity_range
GO
DELETE FROM MSreplication_subscriptions
GO
DELETE FROM MSsubscription_agents
GO

>.
>

[faustino Dina]

Ok,
I tryed the following:
sp_cleanupdbreplicationsp_removedbreplication

sp_removesrvreplication -- Single user mode

[faustino Dina]

I tryed the following:

sp_cleanupdbreplication

sp_removedbreplication

sp_removesrvreplication -- Single user mode

But they don't cleared the triggers created in the replicated tables. So, if
somebody know how to get rid of these triggers automatically I will
appreciate the help

Thanks

Faustino

[Jim Johnston]

Use this script:

DELETE FROM sysobjects
WHERE (xtype = 'TR') AND (name LIKE 'del_%')
GO

DELETE FROM sysobjects
WHERE (xtype = 'TR') AND (name LIKE 'ins_%')
GO

DELETE FROM sysobjects
WHERE (xtype = 'TR') AND (name LIKE 'upd_%')
GO

DELETE FROM sysobjects
WHERE (name LIKE 'sp_ins%') OR
(name LIKE 'sp_sel%') OR
(name LIKE 'sp_upd%')
GO

DELETE FROM sysobjects
WHERE (name LIKE 'conflict_%')
GO

DELETE FROM sysobjects
WHERE (name LIKE 'ctsv_%') AND (xtype = 'V')
GO

DELETE FROM sysobjects
WHERE (name LIKE 'DYN_VIEW%') AND (xtype = 'V')
GO

DELETE FROM sysobjects
WHERE (name LIKE 'cont%') AND (xtype = 'V')
GO

DELETE FROM sysobjects
WHERE (name LIKE 'filt%') AND (xtype = 'V')
GO

DELETE FROM sysobjects
WHERE (name LIKE 'sel_%') AND (xtype = 'P')
GO

DELETE FROM sysobjects
WHERE (name LIKE 'sp_cft_%') AND (xtype = 'P')
GO

DELETE FROM sysobjects
WHERE (name LIKE 'tsvw_%') AND (xtype = 'V')
GO

UPDATE sysobjects
SET replinfo = 0
WHERE (replinfo = 128) AND (xtype = 'U')
GO

V/R Jim Johnston

"faustino Dina" <fd...@matusa.com.mx> wrote in message news:<#K$obQrVD...@TK2MSFTNGP12.phx.gbl>...