Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2003
    Posts
    1

    Unanswered: Clean up replication data

    I have a develop machine for our web site. On this machine I have a copy of a database from the production site. The database on the production site is being replicated. When I copied the database to the develop machine and started testing some new developments I got the following error:
    Microsoft OLE DB Provider for ODBC Drivers error '80040e37'

    [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'sysmergearticles'

    This tells me that some replication data is also copied to the develop database.

    Can anyone tell me how I can clean this data, or if I should copy the databse in a different way. (Now I create a new databse and do an import of the tables and stored procedures)

  2. #2
    Join Date
    May 2002
    Location
    Timbaktu
    Posts
    185
    You could try to remove replication by going under Tools(in EM)--->Replication--->Disable publishing,Distri...If it allows you to do so then it's fine.Or else you could try to first set the replication on your Test system and then remove ot by using the Tools---->Replication.
    Or you may try the script below:

    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

  3. #3
    Join Date
    Nov 2003
    Location
    London
    Posts
    1

    Really useful script

    I've been trying to delete the conflict tables left over after EXEC sp_removereplication

    Your script did it!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •