Conflict Tables

Question

Post reply

Conflict Tables

Shah_hs

Ten Centuries

Points: 1117
More actions
May 16, 2007 at 9:26 am

#177970

When i created new replication, the system created few tables with prefix..'conflict_'. For some reason i had to delete the subscription and rectreate new one on other server. The time new tables were created but the old tables were not removed on removing the subscription. I want to get rid of these tables as they are now not useful as the subscribion is not more. I tried deleting but could not. The system gave me msg..cant delete system tables. Pls.advise me how to remove these tables..?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Clarence VanDyke SSC Enthusiast Points: 194 More actions · Answer 1

I had the same problem with conflict tables. Here is a script that worked for me:

-- Script to drop all conflict tables

-- in the database

-- This script MUST be run when there is no activity in the database

-- CLV 2/21/2007

-- Enter the name of the database with the conflict tables

USE databasename

-- Declare Variables

SET NOCOUNT ON

DECLARE @TableName varchar(255)

declare @sTableName nvarchar(100)

declare @smsg as Varchar(100)

-- Create a table to hold names of the tables to drop

DECLARE TableCursor CURSOR FOR

SELECT name FROM sysobjects

WHERE type = 'U' AND name LIKE '%conflict_%'

OPEN TableCursor

-- Fetch the table names one by one and cycle through dropping them

FETCH NEXT FROM TableCursor INTO @TableName

WHILE @@FETCH_STATUS = 0

BEGIN

-- Load the conflict table names one by one

-- create and execute a query on the fly

-- to drop each of the conflict tables

set @sTableName=@TableName

set @smsg='drop table' + ' ' + @sTableName

exec(@smsg)

FETCH NEXT FROM TableCursor INTO @TableName

END

-- Close and deaallocate the cursor

CLOSE TableCursor

DEALLOCATE TableCursor

Clarence VanDyke

David Anderson-594160 SSC Rookie Points: 43 More actions · Answer 2

I had a similar problem, but I also had to contend with dropping the extra views that SQL Server created. So I took Clarence's script and modified it to include those as well.

The modified script appears below:

-- Script to drop all conflict tables and views

-- in the database

-- This script MUST be run when there is no activity in the database

-- Use this script ONLY to clean up replication that's already been

-- turned off.

-- CLV 2/21/2007, Mod by DAConsult 12/31/2007

-- Enter the name of the database with the conflict tables

USE database

-- Declare Variables

SET NOCOUNT ON

DECLARE @TableName varchar(255)

declare @sTableName nvarchar(100)

declare @smsg as Varchar(100)

declare @Type as varchar(1)

-- Create a table to hold names of the tables to drop

DECLARE TableCursor CURSOR FOR

SELECT name, type FROM sysobjects

WHERE (type = 'U' AND name LIKE '%conflict_%')

OR (type = 'V' AND name LIKE '%ctsv_%')

OR (type = 'V' AND name like '%tsvw_%')

OPEN TableCursor

-- Fetch the table and view names one by one and cycle through

-- dropping them

FETCH NEXT FROM TableCursor INTO @TableName, @Type