April 18, 2012

sp_dropdistpublisher (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_dropdistpublisher(nvarchar @publisher
, bit @no_checks
, bit @ignore_distributor)

MetaData:

   
--
-- Name:
-- sp_dropdistpublisher
--
-- Description:
-- Drops a distribution Publisher.
-- This stored procedure is executed at the Distributor on any database.
--
-- Security:
-- Public
-- Requires Certificate signature for catalog access
--
-- Returns:
-- Success (0) or failure (1)
--
-- Owner:
-- <current owner>

create procedure sys.sp_dropdistpublisher
(
@publisher sysname,
@no_checks bit = 0,
@ignore_distributor bit = 0
)
AS
BEGIN
SET NOCOUNT ON

DECLARE @distributor sysname
DECLARE @distaccount nvarchar(127)
DECLARE @proc nvarchar (255)
DECLARE @retcode int
DECLARE @privilege sysname
DECLARE @return_status int
DECLARE @found int
DECLARE @distribdb sysname
DECLARE @command nvarchar(255)
DECLARE @active_value int
DECLARE @publish_bit int
DECLARE @mergepub_bit int
DECLARE @vendor sysname
DECLARE @publisher_type sysname
DECLARE @tempdistr sysname
DECLARE @tempdistrdb sysname
DECLARE @cmd nvarchar(255)
DECLARE @publisher_db sysname
DECLARE @hrepl bit
DECLARE @sa_login sysname

SELECT @found = 0
SELECT @hrepl = 1

SELECT @publish_bit = 1
SELECT @mergepub_bit = 4

--
-- Security Check: require sysadmin
--
IF (ISNULL(IS_SRVROLEMEMBER('sysadmin'),0) = 0)
BEGIN
RAISERROR(21089,16,-1)
RETURN (1)
END

--
-- Parameter Check: @publisher.
-- Check to make sure that the publisher exists, that the name isn't
-- NULL, and that the name conforms to the rules for identifiers.
--

IF @publisher IS NULL
BEGIN
RAISERROR (14043, 16, -1, '@publisher', 'sp_dropdistpublisher')
RETURN (1)
END

EXECUTE @retcode = sys.sp_validname @publisher

IF @retcode <> 0
BEGIN
RETURN (1)
END

-- Make publisher name case insensitive
SELECT @publisher = UPPER(@publisher) COLLATE DATABASE_DEFAULT

--
-- Get distribution server information for remote RPC
-- agent verification.
--
EXEC @retcode = sys.sp_MSrepl_getdistributorinfo @publisher = @publisher,
@distributor = @distributor OUTPUT,
@distribdb = @distribdb OUTPUT,
@publisher_type = @publisher_type OUTPUT

IF @@error <> 0 OR @retcode <> 0 OR @distributor IS NULL
BEGIN
RAISERROR (14071, 16, -1)
RETURN (1)
END

--
-- Only a local distributor can be modified.
--
IF UPPER(@distributor) <> UPPER(@@SERVERNAME)
BEGIN
RAISERROR (14114, 16, -1, @@SERVERNAME)
RETURN (1)
END

-- Verify publisher exists
IF NOT EXISTS
(
SELECT *
FROM msdb..MSdistpublishers
WHERE UPPER(name collate database_default) = UPPER(@publisher) collate database_default
)
BEGIN
RAISERROR (14080, 11, -1, @publisher)
RETURN (1)
END

IF @publisher_type = N'MSSQLSERVER' AND UPPER(@publisher) = UPPER(@@SERVERNAME)
BEGIN
--
-- If @publisher is local,
-- 1. check to make sure there is no subscriber for publisher in distribution database
-- 2. check to make sure there no databases enabled for replication
--
IF @ignore_distributor = 0
BEGIN
-- Check to see if there are subscribers defined.
EXEC @retcode = sys.sp_MSrepl_helpsubscriberinfo @publisher = @publisher,
@subscriber = N'%',
@found = @found OUTPUT

IF @retcode <> 0 OR @@ERROR <> 0
BEGIN
RETURN (1)
END

IF @found = 1
BEGIN
RAISERROR(21047, 16, -1)
RETURN(1)
END
END

-- Check to see if any db's enabled for replication
IF EXISTS
(
SELECT *
FROM master.dbo.sysdatabases
WHERE (category & @publish_bit) <> 0
OR (category & @mergepub_bit) <> 0
)
BEGIN
RAISERROR (21033, 16, -1, @@SERVERNAME)
RETURN (1)
END
END
ELSE
BEGIN
IF @no_checks = 0
BEGIN
--
-- If the publisher is remote, check the status of the distpublisher
-- The status will be inactive if the remote publisher dropped the
-- distributor.
--
IF @publisher_type = N'MSSQLSERVER'
BEGIN
IF EXISTS
(
SELECT *
FROM msdb.dbo.MSdistpublishers
WHERE UPPER(name collate database_default) = UPPER(@publisher) collate database_default
AND active = 1
)
BEGIN
RAISERROR (14098, 16, -1, @publisher, @@SERVERNAME)
RETURN (1)
END
END
END
END

IF @ignore_distributor = 0
BEGIN
IF NOT @publisher_type = N'MSSQLSERVER'
BEGIN
SELECT @command = QUOTENAME(@distribdb) + '.sys.sp_IHdroppublisher'
exec @retcode = @command @publisher, @publisher_type, @no_checks

IF ((@retcode != 0) OR (@@ERROR != 0)) AND (@no_checks = 0)
BEGIN
-- Note failure and return an error at the end
SET @hrepl = 0
END
END

SELECT @command = QUOTENAME(@distribdb) + '.dbo.sp_MSdistpublisher_cleanup'
EXEC @retcode = @command @publisher
IF @retcode <> 0 or @@error <> 0
BEGIN
RETURN(1)
END
END

DECLARE @fExists int

-- get the servername that was persisted in the sysservers table
SELECT @publisher = sys.fn_getpersistedservernamecasevariation(@publisher) collate database_default
SELECT @sa_login = SUSER_SNAME(0x01)


EXEC @fExists = sys.sp_MSIfExistsRemoteLogin @publisher,
'distributor_admin',
@sa_login

IF (@fExists = 1)
BEGIN
EXEC @retcode = sys.sp_dropremotelogin @publisher,
'distributor_admin',
@sa_login

IF @@ERROR <> 0 OR @retcode <> 0
BEGIN
RETURN (1)
END
END

EXEC @fExists = sys.sp_MSIfExistsRemoteLogin @publisher,
'distributor_admin',
'distributor_admin'

IF (@fExists = 1)
BEGIN
EXECUTE @retcode = sys.sp_dropremotelogin @publisher,
'distributor_admin',
'distributor_admin'

IF @@ERROR <> 0 OR @retcode <> 0
BEGIN
RETURN (1)
END
END

-- store entry prior to delete so we can manually rollback
-- in case of a failure durring the remaining steps. we can
-- not use transactions since sp_dropserver can not be exec'd
-- within a transaction... and we can not wait on the delete
-- till after the dropserver since drop server will check for
-- this entry and fail if there is a distpub for this server
SELECT *
INTO #MSdistpublishers
FROM msdb..MSdistpublishers
WHERE UPPER(name collate database_default) = UPPER(@publisher) collate database_default

-- Remove the publisher now that most of cleanup has completed
-- only thing that remains is the dop of server...
DELETE msdb..MSdistpublishers
WHERE UPPER(name collate database_default) = UPPER(@publisher) collate database_default
IF @@ERROR <> 0
BEGIN
RETURN (1)
END

IF (@publisher_type = N'MSSQLSERVER')
BEGIN
--
-- SQL Server specific
-- drop RPC server entry for remote publisher
--
IF (upper(@distributor) != upper(@publisher))
BEGIN
IF EXISTS
(
SELECT *
FROM master.dbo.sysservers
WHERE upper(srvname collate database_default) = upper(@publisher)
)
BEGIN
EXEC @retcode = sys.sp_dropserver @publisher, 'droplogins'

IF (@@error != 0 or @retcode != 0)
BEGIN
GOTO FAILURE
END
END
END
END
ELSE
BEGIN
-- For heterogeneous publishers drop the remote server and it's logins
exec @retcode = sys.sp_dropserver @publisher, 'droplogins'

IF @@ERROR <> 0 OR @retcode <> 0
BEGIN
GOTO FAILURE
END

-- Drop expired subscription cleanup job and alerts
EXEC @retcode = sys.sp_MSrepl_drop_expired_sub_cleanup_job @publisher

IF (@@ERROR != 0 OR @retcode != 0)
BEGIN
GOTO FAILURE
END
END

-- Override status and return an error if HREPL failed.
-- Post-hrepl steps will have completed and left the distributor
-- in a consistent state.
IF @hrepl = 0
BEGIN
-- Report failure to drop publisher info but don't finish cleanup
RAISERROR(21749, 16, -1, @publisher)
GOTO FAILURE
END

RETURN (0)
FAILURE:
INSERT INTO msdb..MSdistpublishers
SELECT *
FROM #MSdistpublishers

RETURN (1)
END

No comments:

Post a Comment

Total Pageviews