May 11, 2012

sp_MSdropmergedynamicsnapshotjob (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_MSdropmergedynamicsnapshotjob(nvarchar @publication
, nvarchar @dynamic_snapshot_jobname
, uniqueidentifier @dynamic_snapshot_jobid
, bit @ignore_distributor)

MetaData:

 --   
-- Name: sp_MSdropmergedynamicsnapshotjob
--
-- Description: This procedure drops a scheduled dynamic snapshot job for
-- a publication and the associated meta-data in
-- MSdynamicsnapshotjobs. This procedure will also remove all
-- files in the associated dynamic snapshot location.
--
-- Parameters: @publication sysname (mandatory)
-- @dynamic_snapshot_jobname (optional, default '%')
-- @dynamic_snapshot_jobid uniqueidentifier (optional, default
-- null) When @dynamic_snapshot_jobid is null and
-- @dynamic_snapshot_jobname is '%', all dynamic snapshot
-- jobs for the specified publication will be dropped.
-- @ignore_distributor bit (optional, default 0)
--
-- Notes: 1) At most one of @dynamic_snapshot_jobid and
-- @dynamic_snapshot_jobname can be specified with a non-default
-- value.
--
-- Returns: 0 - succeeded
-- 1 - failed
--
-- Security: Only members of the 'sysadmin' server role and the 'db_owner'
-- database role can execute this procedure successfully even though execute
-- permission of this procedure is granted to public.
-- Requires Certificate signature for catalog access
--
create procedure sys.sp_MSdropmergedynamicsnapshotjob (
@publication sysname,
@dynamic_snapshot_jobname sysname = '%',
@dynamic_snapshot_jobid uniqueidentifier = null,
@ignore_distributor bit = 0
)
as
begin
set nocount on

declare @retcode int
declare @pubid uniqueidentifier
declare @dynamic_snapshot_location nvarchar(255)
declare @guidstr nvarchar(40)
declare @dynamic_snapshot_jobid_from_cursor uniqueidentifier
declare @dir nvarchar(4000)

declare @distributor sysname
declare @distribdb sysname
declare @rpcsrvname sysname
declare @distproc nvarchar(4000)
declare @publisher sysname
declare @publisher_db sysname
declare @agent_id int
declare @backward_comp_level int
declare @dynamic_filter_login sysname
declare @dynamic_filter_hostname sysname
declare @partition_id int

select @retcode = 0
select @pubid = null
select @dynamic_snapshot_location = null
select @publisher = publishingservername()
select @publisher_db = db_name()


if object_id('sysmergepublications') is NULL
begin
raiserror (20054, 16, -1)
return (1)
end

-- Make sure that caller is a member of 'sysadmins' or 'db_owner'
exec @retcode = sys.sp_MSreplcheck_publish
if @@error <> 0 or @retcode <> 0
return (1)

select @pubid = pubid,
@backward_comp_level = backward_comp_level
from dbo.sysmergepublications
where name = @publication
and upper(publisher) = upper(publishingservername())
and publisher_db = db_name()

if @pubid is null
begin
raiserror(20026, 16, -1, @publication)
return (1)
end

-- At most one of @dynamic_snapshot_jobid and @dynamic_snapshot_jobname
-- can be specified with a non-default value
if @dynamic_snapshot_jobid is not null and
@dynamic_snapshot_jobname <> N'%'
begin
raiserror(21329, 16, -1)
return (1)
end

if @dynamic_snapshot_jobid is null and @dynamic_snapshot_jobname = N'%'
begin

declare hJobsCursor cursor local fast_forward for
select job_id
from MSdynamicsnapshotjobs
where pubid = @pubid
if @@error <> 0
return 1

open hJobsCursor
if @@error <> 0
return 1


fetch hJobsCursor into @dynamic_snapshot_jobid_from_cursor

begin transaction
save transaction sp_MSdropdynamicsnapshotjobC

while (@@fetch_status <> -1)
begin
if @dynamic_snapshot_jobid_from_cursor is not null
begin
exec @retcode = sys.sp_MSdropmergedynamicsnapshotjob
@publication = @publication,
@dynamic_snapshot_jobid = @dynamic_snapshot_jobid_from_cursor,
@ignore_distributor = @ignore_distributor
if @@error <> 0 or @retcode <> 0
goto CursorFailure
end
fetch hJobsCursor into @dynamic_snapshot_jobid_from_cursor
end

commit transaction
close hJobsCursor
deallocate hJobsCursor
return 0
CursorFailure:


rollback transaction sp_MSdropdynamicsnapshotjobC
commit transaction
close hJobsCursor
deallocate hJobsCursor
return 1
end

select @pubid = pubid
from dbo.sysmergepublications
where name = @publication
and upper(publisher) = upper(publishingservername())
and publisher_db = db_name()

if @dynamic_snapshot_jobid is null
begin
select @dynamic_snapshot_location = dynamic_snapshot_location,
@dynamic_snapshot_jobid = job_id,
@dynamic_filter_login = dynamic_filter_login,
@dynamic_filter_hostname = dynamic_filter_hostname,
@partition_id = @partition_id,
@agent_id = agent_id
from dbo.MSdynamicsnapshotjobs
where pubid = @pubid
and name = @dynamic_snapshot_jobname
end
else
begin
select @dynamic_snapshot_location = dynamic_snapshot_location,
@dynamic_filter_login = dynamic_filter_login,
@dynamic_filter_hostname = dynamic_filter_hostname,
@partition_id = @partition_id,
@agent_id = agent_id
from dbo.MSdynamicsnapshotjobs
where pubid = @pubid
and job_id = @dynamic_snapshot_jobid
end

if @dynamic_snapshot_location is null
begin
if @dynamic_snapshot_jobid is null
begin
raiserror(21326, 16, -1, N'@dynamic_snapshot_jobname', @dynamic_snapshot_jobname)
end
else
begin
select @guidstr = coalesce(convert(nvarchar(40), @dynamic_snapshot_jobid) collate database_default, '(NULL)' collate database_default)
raiserror(21326, 16, -1, N'@dynamic_snapshot_jobid', @guidstr)
end
return (1)
end

-- Get distributor info for RPC
if @ignore_distributor = 0
begin
exec @retcode = sys.sp_helpdistributor @distributor = @distributor output,
@distribdb = @distribdb output,
@rpcsrvname = @rpcsrvname output

select @rpcsrvname = rtrim(@rpcsrvname)


select @distproc = QUOTENAME(RTRIM(@rpcsrvname)) + N'.' + QUOTENAME(@distribdb) + N'.dbo.sp_MScleanupdynamicsnapshotfolder'
exec @retcode = @distproc
@publisher = @publisher,
@publisher_db = @publisher_db,
@publication = @publication,
@dynamic_filter_login = @dynamic_filter_login,
@dynamic_filter_hostname = @dynamic_filter_hostname,
@dynamic_snapshot_location = @dynamic_snapshot_location,
@partition_id = @partition_id

if @backward_comp_level < 90 and isnull(is_srvrolemember('sysadmin'),0) = 1
begin
select @distproc = QUOTENAME(RTRIM(@rpcsrvname)) + N'.' + QUOTENAME(@distribdb) + N'.' + N'dbo.sp_MSdeletefoldercontents'
-- Try to delete the files
select @dir = @dynamic_snapshot_location
exec @retcode = @distproc @dir
-- Ignore errors
end
end
select @retcode = 0

begin transaction
save transaction sp_MSdropmergedynamicsnapshotjob

-- Delete row from MSdynamicsnapshotjobs
delete dbo.MSdynamicsnapshotjobs
where pubid = @pubid
and job_id = @dynamic_snapshot_jobid

if @@error <> 0
goto Failure

if @ignore_distributor = 0
begin
-- Drop the dynamic snapshot job at the distributor
select @distproc = QUOTENAME(RTRIM(@rpcsrvname)) + N'.' + QUOTENAME(@distribdb) + N'.' + N'dbo.sp_MSdrop_dynamic_snapshot_agent'

exec @retcode = @distproc @publisher, @publisher_db, @publication, @agent_id
if @retcode <> 0 or @@error <> 0
goto Failure
end
commit transaction
return 0

Failure:
rollback transaction sp_MSdropmergedynamicsnapshotjob
commit transaction
return 1
end

No comments:

Post a Comment

Total Pageviews