-- Name: sp_MSrepl_clean_replication_bit
-- Descriptions: clean the replication bit from a database
-- Parameters: as defined in create statement
-- Security:
-- Procedure is NOT PUBLIC so there are no security checks.
CREATE procedure sys.sp_MSrepl_clean_replication_bit(@dbname sysname,@type nvarchar(5))
as
DECLARE @flush_proc nvarchar(300),
@done_proc nvarchar(300)
IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) IN ('tran', 'both')
BEGIN
-- Clear tran bit
IF DatabasePropertyEx(@dbname, 'IsPublished') <> 1
begin
EXEC %%DatabaseEx(Name = @dbname).SetPublished(Value = 1)
end
/*
** Toggle the category bit in master.dbo.sysdatabases
*/
IF CONVERT(sysname,DATABASEPROPERTYEX(@dbname,'Updateability')) = N'READ_WRITE'
AND HAS_DBACCESS(@dbname) = 1
BEGIN
SELECT @flush_proc = QUOTENAME(@dbname) + N'.sys.sp_replflush'
SELECT @done_proc = QUOTENAME(@dbname) + N'.sys.sp_repldone'
--when the DB is not enabled for CDC, call sp_repldone and sp_replflush
IF not exists(select * from sys.databases where db_id(@dbname) = database_id and is_cdc_enabled = 1)
BEGIN
EXEC @flush_proc
EXEC @done_proc NULL, NULL, 0, 0, 1
END
--when the DB is still enabled for CDC, sp_replflush should be called at least
EXEC @flush_proc
END
EXEC %%DatabaseEx(Name = @dbname).SetPublished(Value = 0)
END
IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) IN ('merge', 'both')
BEGIN
-- Clear merge bit
IF DatabasePropertyEx(@dbname, 'IsMergePublished') = 1
begin
/*
** Toggle the category bit in master.dbo.sysdatabases
*/
EXEC %%DatabaseEx(Name = @dbname).SetMergePublished(Value = 0)
end
END
return 0