May 16, 2007 at 9:26 am
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..?
June 25, 2007 at 2:58 pm
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
December 31, 2007 at 12:17 pm
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
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
IF @Type = 'V'
set @smsg='drop view' + ' ' + @sTableName
ELSE
set @smsg='drop table' + ' ' + @sTableName
exec(@smsg)
FETCH NEXT FROM TableCursor INTO @TableName, @Type
END
-- Close and deaallocate the cursor
CLOSE TableCursor
DEALLOCATE TableCursor
Hope it helps,
David Anderson
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy