create procedure sys.sp_MSrepl_dropsubscriber
(
@subscriber sysname,
@reserved nvarchar(50),
@ignore_distributor bit,
@publisher sysname,
@noraise int = NULL
)
AS
BEGIN
SET NOCOUNT ON
/*
** Declarations.
*/
DECLARE @db_name sysname
DECLARE @foundSubscription int
DECLARE @ClearSubStatus int
DECLARE @command nvarchar(255)
DECLARE @transpublishdb_bit int
DECLARE @mergepublishdb_bit int
DECLARE @distdb_bit int
DECLARE @distributor sysname
DECLARE @distribdb sysname
DECLARE @distproc nvarchar (300)
DECLARE @retcode int
DECLARE @type nvarchar(10)
DECLARE @publisher_local sysname
SELECT @transpublishdb_bit = 1
SELECT @mergepublishdb_bit = 4
SELECT @distdb_bit = 16
SELECT @ClearSubStatus = 1
-- 'sysadmin' Security check was moved to sp_dropsubscriber.
-- NOTE: sp_MSrepl_dropsubscriber is also called directly by sp_dropsubscription.
-- 'db_owner' is sufficient authorization when called in this manner.
/*
** Security Check: require sysadmin or DBO of publishing database
*/
IF (ISNULL(IS_SRVROLEMEMBER('sysadmin'),0) = 0) AND
(ISNULL(IS_MEMBER('db_owner'),0) = 0)
BEGIN
RAISERROR(21050,16,-1)
RETURN (1)
END
/*
** If @publisher is NULL, set @publisher_local to @@SERVERNAME
** otherwise, set @publisher_local to @publisher
*/
IF @publisher IS NULL
BEGIN
select @publisher_local = publishingservername()
END
ELSE
BEGIN
/* For heterogeneous publisher, the current database must be the */
/* distribution db for the publisher */
select @publisher_local = @publisher
IF NOT EXISTS
(
SELECT *
FROM master.dbo.sysservers s,
msdb..MSdistpublishers m
WHERE UPPER(s.srvname collate database_default) = UPPER(m.name collate database_default)
AND UPPER(s.srvname collate database_default) = UPPER(@publisher) collate database_default
AND UPPER(m.distribution_db collate database_default) = UPPER(DB_NAME()) collate database_default
)
BEGIN
RETURN (1)
END
END
/*
** Parameter Check: @subscriber.
** Check to make sure that the subscriber exists.
*/
IF @subscriber IS NULL
BEGIN
RAISERROR (14043, 16, -1, '@subscriber', 'sp_MSrepl_dropsubscriber')
RETURN (1)
END
EXECUTE @retcode = sys.sp_validname @subscriber
IF @retcode <> 0
BEGIN
RETURN (1)
END
IF LOWER(@subscriber) = 'all'
BEGIN
DECLARE #hCdropsubscriber_all CURSOR LOCAL FAST_FORWARD FOR
SELECT srvname
FROM master.dbo.sysservers
WHERE srvstatus & 4 <> 0
FOR READ ONLY
OPEN #hCdropsubscriber_all
FETCH #hCdropsubscriber_all INTO @subscriber
WHILE (@@fetch_status <> -1)
BEGIN
EXECUTE @retcode = sys.sp_dropsubscriber @subscriber = @subscriber,
@publisher = @publisher,
@ignore_distributor = @ignore_distributor,
@reserved = @reserved
IF @@ERROR <> 0 OR @retcode <> 0
RETURN (1)
FETCH #hCdropsubscriber_all INTO @subscriber
end
return(0)
end
/*
** There should be no subscription by the subscriber
** Open a cursor the published databases.
**
*/
IF @publisher IS NULL
BEGIN
DECLARE #hCdropsubscriber CURSOR LOCAL FAST_FORWARD FOR
SELECT name,
N'tran'
FROM master.dbo.sysdatabases
WHERE (category & @transpublishdb_bit) <> 0
and databasepropertyex(name, 'Updateability') = 'READ_WRITE'
and has_dbaccess(name) = 1
UNION
SELECT name, N'merge' from master.dbo.sysdatabases
WHERE (category & @mergepublishdb_bit) <> 0
and databasepropertyex(name, 'Updateability') = 'READ_WRITE'
and has_dbaccess(name) = 1
FOR READ ONLY
OPEN #hCdropsubscriber
FETCH #hCdropsubscriber INTO @db_name, @type
WHILE (@@fetch_status <> -1)
BEGIN
IF LOWER(@reserved) = N'drop_subscriptions'
BEGIN
if @type = N'tran'
begin
SELECT @command = quotename(@db_name) + N'.sys.sp_dropsubscription'
EXECUTE @retcode = @command
@publication = N'all',
@article = N'all',
@subscriber = @subscriber,
@publisher = @publisher,
@ignore_distributor = @ignore_distributor
end
if @type = 'merge'
begin
SELECT @command = quotename(@db_name) + N'.sys.sp_dropmergesubscription'
EXECUTE @retcode = @command
@publication = N'all',
@subscriber = @subscriber,
@subscription_type = N'both',
@ignore_distributor = @ignore_distributor
end
IF @@ERROR <> 0 OR @retcode <> 0
RETURN (1)
END
-- Set @foundSubscription to not null so that sp_helpsubscription will not display
-- result set
select @foundSubscription = 0
if @type = 'tran'
begin
SELECT @command = quotename(@db_name) + N'.sys.sp_helpsubscription'
EXECUTE @retcode = @command
@publisher = @publisher,
@publication = N'%',
@article = N'%',
@subscriber = @subscriber,
@found = @foundSubscription OUTPUT
IF @@ERROR <> 0 OR @retcode <> 0
RETURN (1)
end
else
begin
SELECT @command = quotename(@db_name) + '.sys.sp_helpmergesubscription'
EXECUTE @retcode = @command @publication = N'%',
@subscriber = @subscriber,
@subscription_type = N'both',
@found = @foundSubscription OUTPUT
IF @@ERROR <> 0 OR @retcode <> 0
RETURN (1)
end
IF @foundSubscription <> 0
BEGIN
IF ISNULL(@noraise, 0) = 0
BEGIN
RAISERROR ( 14144, 16, -1, @subscriber, @db_name)
RETURN (1)
END
-- Return without error if @noraise = 1
-- Used when called from sp_dropsubscription to drop
-- subscriber when all subscriptions have been dropped.
RETURN (0)
END
FETCH #hCdropsubscriber INTO @db_name, @type
END
END
ELSE
BEGIN
IF LOWER(@reserved) = N'drop_subscriptions'
BEGIN
-- For heterogeneous publishers execute sp_dropsubscription for the publisher and
-- subscriber in the current database.
-- @ignore_distributor should always be set to 0 since this will always execute
-- on the Oracle distributor.
SELECT @command = N'sys.sp_dropsubscription'
EXECUTE @retcode = @command
@publication = N'all',
@article = N'all',
@subscriber = @subscriber,
@publisher = @publisher,
@ignore_distributor = 0
IF @@ERROR <> 0 OR @retcode <> 0
RETURN (1)
END
SELECT @foundSubscription = 0
SELECT @db_name = DB_NAME()
SELECT @command = N'sys.sp_helpsubscription'
EXECUTE @retcode = @command
@publisher = @publisher,
@publication = N'%',
@article = N'%',
@subscriber = @subscriber,
@found = @foundSubscription OUTPUT
IF @foundSubscription <> 0
BEGIN
IF ISNULL(@noraise, 0) = 0
BEGIN
RAISERROR ( 14144, 16, -1, @subscriber, @db_name)
RETURN (1)
END
-- Return without error if @noraise = 1
-- Used when called from sp_dropsubscription to drop
-- subscriber when all subscriptions have been dropped.
RETURN (0)
END
IF @@ERROR <> 0 OR @retcode <> 0
RETURN (1)
END
/*
** Drop the subsubscriber_info in the distribution database
*/
/*
** if @ignore_distributor = 1, we are in bruteforce cleanup mode, don't do RPC.
*/
if @ignore_distributor = 0
begin
/*
** Get distribution server information for remote RPC
** agent verification.
*/
EXEC @retcode = sys.sp_MSrepl_getdistributorinfo @publisher = @publisher,
@rpcsrvname = @distributor OUTPUT,
@distribdb = @distribdb OUTPUT
IF @@error <> 0 OR @retcode <> 0
BEGIN
RAISERROR (14071, 16, -1)
RETURN (1)
END
/*
** Delete information from MSsubscriber_info in the distribution db
*/
if @distribdb is not null
begin
SELECT @distproc = QUOTENAME(RTRIM(@distributor)) + '.' +
QUOTENAME(RTRIM(@distribdb)) +
'.dbo.sp_MSdrop_subscriber_info'
EXEC @retcode = @distproc
@publisher = @publisher_local,
@subscriber = @subscriber
IF @@error <> 0 OR @retcode <> 0
BEGIN
RAISERROR (14042, 16, -1)
RETURN (1)
END
end
end
-- We have already validated the existence of the server at this point
SELECT @subscriber = sys.fn_getpersistedservernamecasevariation(@subscriber) collate database_default
IF @distribdb IS NOT NULL AND UPPER(@@SERVERNAME) = UPPER(@distributor)
BEGIN
-- Determine whether SUBSCRIBER bit in SRVSTATUS can be cleared
EXECUTE @ClearSubStatus = sys.sp_MSDropCanClearSubscriberStatus @publisher_local,
@distributor,
@subscriber,
@distribdb
END
IF @ClearSubStatus = 1
BEGIN
-- Turn off the subscriber server option(s)
SELECT @subscriber = sys.fn_getpersistedservernamecasevariation(@subscriber) collate database_default
if (@subscriber is not null)
EXEC @retcode = sys.sp_MSdrop_subserver @subscriber = @subscriber
IF @@error <> 0 OR @retcode <> 0
BEGIN
RETURN (1)
END
END
RAISERROR (14062, 10, -1)
END