create procedure sys.sp_MSdropmergepub_snapshot (
@publication sysname,
@ignore_distributor bit = 0
) AS
SET NOCOUNT ON
/*
** Declarations.
*/
declare @retcode int
declare @distributor sysname
declare @distproc nvarchar(300)
declare @snapshot_jobid binary(16)
declare @fFoundPublication int
declare @pubid uniqueidentifier
declare @distribdb sysname
,@publishingservername sysname
/*
** Initializations
*/
select @fFoundPublication = 0
,@publishingservername = publishingservername()
/* validate the publication */
/* If the publication is not exist found return error */
EXEC @retcode = sys.sp_helpmergepublication @publication, @fFoundPublication output, @pubid output
if @@ERROR <> 0 OR @fFoundPublication = 0 OR @retcode <> 0
BEGIN
RETURN (1)
END
/*
** Get taskid. Make sure the snapshot_jobid is not NULL only before using MAX
** Otherwise there will be a warnning.
*/
select @snapshot_jobid = max(isnull(snapshot_jobid,0)) FROM dbo.sysmergepublications WHERE pubid = @pubid
if (@snapshot_jobid IS NOT NULL)
begin
/*
** if @ignore_distributor = 1, we are in bruteforce cleanup mode, don't do RPC.
*/
if @ignore_distributor = 0
begin
/*
** Get distributor information
*/
EXEC @retcode = sys.sp_helpdistributor @rpcsrvname = @distributor OUTPUT,
@distribdb = @distribdb OUTPUT
if @@error <> 0 OR @retcode <> 0
BEGIN
RAISERROR (14071, 16, -1)
RETURN (1)
END
/*
** Delete sync agent of Publication.
*/
declare @dbname sysname
set @dbname = DB_NAME()
SELECT @distproc = RTRIM(@distributor) + '.' + QUOTENAME(@distribdb) + '.dbo.sp_MSdrop_snapshot_agent'
EXECUTE @retcode = @distproc
@publisher = @publishingservername,
@publisher_db = @dbname,
@publication = @publication
IF @@ERROR <> 0 or @retcode <> 0
RETURN(1)
end
/* Update publication's taskid */
UPDATE dbo.sysmergepublications set snapshot_jobid = NULL WHERE pubid = @pubid
IF @@ERROR <> 0
BEGIN
RAISERROR (20072, 16, -1)
RETURN (1)
END
end
return (0)