May 8, 2012

sp_MScreatemergedynamicsnapshot (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_MScreatemergedynamicsnapshot(nvarchar @publication)

MetaData:

 --   
-- Name: sp_MScreatemergedynamicsnapshot
--
-- Description:
-- This procedure checks if a dynamic snapshot job is available
-- with the given filter criterion. If not it creates such a job
-- by calling sp_MSaddmergedynamicsnapshotjob. If one already
-- exists, it starts this job and subsequently waits for it complete.
-- The procedure returns success or failure depending on whether the
-- job succeded or failed.
--
-- Notes: 1) The dynamic snapshot location is computed from the working directory
-- on the distributor (got from sp_helpdistributor)
-- or the alternate snapshot folder for the publication if one is specified.
-- this is done by calling proc sp_MSgetpartitionsnapshotfolder
-- 2) This procedure will not check whether the given path is already in
-- use by another dynamic snapshot generation job. Sharing the same
-- dynamic snapshot location among different dynamic snapshot
-- generation jobs can lead to file corruption and/or snapshot files
-- being overwritten.
-- 3) calls sp_MSaddmergedynamicsnapshotjob to add the job if it does not exist
-- 4) starts the dynamic snapshot job and waits for it to finish. Checks if the
-- job finished by attempting to acquire the applock that the dynamic snapshot
-- will release when it is done.
--
-- Parameters: @publication sysname (mandatory)
--
-- Returns: 0 - succeeded
-- 1 - failed
--
-- Security: Only users in the PAL role for the publication @publication or
-- db_owners or sysadmins can run this proc.
-- Requires Certificate signature for catalog access
--
create procedure sys.sp_MScreatemergedynamicsnapshot (
@publication sysname
)
AS
declare @retcode int
declare @pub_uses_host_name_for_filtering bit
declare @pub_uses_suser_sname_for_filtering bit
declare @host_name sysname
declare @suser_sname sysname
declare @jobid uniqueidentifier
declare @pubid uniqueidentifier
declare @snapshot_location nvarchar(255)
declare @partition_id int
declare @when_generated datetime
declare @got_applock int
declare @wait_counter int
declare @agentid int
declare @timestamp timestamp

--
-- Security Check and parameter check for @publication
--
SELECT @pubid = NULL
exec @retcode = sys.sp_MSmerge_validate_publication_presence @publication, NULL, NULL, @pubid output
if @retcode <> 0 or @@error <> 0
return 1

set @pub_uses_host_name_for_filtering = 0
set @pub_uses_suser_sname_for_filtering = 0
select @host_name = NULL
select @suser_sname = NULL
exec sys.sp_MSget_dynamic_filtering_information @pubid = @pubid,
@uses_host_name = @pub_uses_host_name_for_filtering OUTPUT,
@uses_suser_sname = @pub_uses_suser_sname_for_filtering OUTPUT
if @@error <> 0
goto FAILURE

if @pub_uses_host_name_for_filtering = 1
select @host_name = HOST_NAME()

if @pub_uses_suser_sname_for_filtering = 1
select @suser_sname = SUSER_SNAME()

-- find the partition id for the subscriber
-- deriving the partition id here and not accepting it as a parameter (from the merge agent)
-- helps in avoiding false identity.
select @partition_id = -1
exec @retcode = sys.sp_MSget_subscriber_partition_id
@publication = @publication,
@partition_id = @partition_id OUTPUT,
@maxgen_whenadded = NULL,
@host_name_override = @host_name,
@suser_sname_override = @suser_sname
if @@error <> 0 or @retcode <> 0 or @partition_id = -1
begin
raiserror(20629, 16, -1)
return 1
end

-- check if a job already exists for the given dynamic_filter_login and dynamic_filter_hostname
select @jobid = NULL
select @jobid = job_id, @agentid = agent_id from dbo.MSdynamicsnapshotjobs
where pubid = @pubid and
((@suser_sname is NULL and dynamic_filter_login is NULL) or dynamic_filter_login = @suser_sname) and
((@host_name is NULL and dynamic_filter_hostname is NULL) or dynamic_filter_hostname = @host_name)
if (@jobid is NULL)
begin
-- we need to create the job here
-- sp_MSaddmergedynamicsnapshotjob will select the location correctly if none is specified.
select @snapshot_location = NULL

-- set the frequency type of the job to 1 to indicate run on demand
exec @retcode = sys.sp_MSaddmergedynamicsnapshotjob
@publication = @publication,
@dynamic_filter_login = @suser_sname,
@dynamic_filter_hostname = @host_name,
@dynamic_snapshot_location = @snapshot_location,
@dynamic_snapshot_jobname = NULL,
@dynamic_snapshot_jobid = @jobid OUTPUT,
@frequency_type = 1,
@dynamic_snapshot_agentid = @agentid output,
@ignore_select = 1
if @@error <> 0 or @retcode <> 0 or @jobid is NULL
begin
raiserror(20632, 16, -1)
return 1
end
end
else
begin
select @got_applock = -1
-- now that the job exists check if someone else is running the dynamic snapshot now
-- check this by attempting to obtain the dynamic snapshot applock with zero wait.
exec @retcode = sys.sp_MSgetdynamicsnapshotapplock @publication, @partition_id, @got_applock OUTPUT, 0
if @@error <> 0 or @retcode <> 0
goto FAILURE

-- if we got the applock it means that the job is not already running and hence we need to start it
if @got_applock >= 0
begin
exec @retcode = sys.sp_MSreleasedynamicsnapshotapplock @publication, @partition_id
if @@error <> 0
goto FAILURE
end
else
begin
-- if we did not get the applock wait for the applock till 2000. Once we get
-- the applock check once more to see if the dynamic snapshot location has been updated.
exec @retcode = sys.sp_MSgetdynamicsnapshotapplock @publication, @partition_id, @got_applock OUTPUT, 2000
if @@error <> 0 or @retcode <> 0
goto FAILURE
if @got_applock >= 0
begin
exec @retcode = sys.sp_MSreleasedynamicsnapshotapplock @publication, @partition_id
if @@error <> 0
goto FAILURE

select @snapshot_location = NULL
select @snapshot_location = dynamic_snapshot_location from dbo.MSmerge_dynamic_snapshots where partition_id = @partition_id
if @snapshot_location is not NULL
begin
-- select @dynamic_snapshot_location = @snapshot_location
select @snapshot_location, 0, 0x00000
return 0
end
end
end
end

-- start the dynamic snapshot job on the distributor
-- Get distributor information for RPC
declare @rpcsrvname sysname
declare @distributor sysname
declare @distproc nvarchar(300)
declare @distribdb sysname
declare @retcode2 int
declare @command nvarchar(1000)

exec @retcode = sys.sp_MSrepl_getdistributorinfo @distributor = @distributor output,
@distribdb = @distribdb output,
@rpcsrvname = @rpcsrvname output
if @@error <> 0 or @retcode <> 0
goto FAILURE

-- before starting the job get the max timestamp from the MSsnaphsot_history table so that
-- we can look for history information higher than that timestamp
select @distproc = quotename(rtrim(@rpcsrvname)) + '.' + quotename(@distribdb) + '.sys.sp_executesql '
select @command = N' exec @retcode2 = ' + quotename(@distribdb) + '.sys.sp_MSgetmaxsnapshottimestamp ' + convert(nvarchar(5),@agentid) + ', @timestamp output'
EXEC @retcode = @distproc @command, N'@timestamp timestamp output, @retcode2 int output', @retcode2=@retcode2 output, @timestamp=@timestamp output
if @@error <> 0 or @retcode <> 0 or @retcode2 <> 0
begin
raiserror(20655, 16, -1)
goto FAILURE
end

select @distproc = rtrim(@rpcsrvname) + N'.msdb.dbo.sp_start_job'
exec @retcode = @distproc @job_id = @jobid
if @@error <> 0 or @retcode <> 0
begin
raiserror(20633, 16, -1)
goto FAILURE
end

-- now return the the job id so that the merge agent can monitor the job
select '', @agentid, @timestamp
return 0

FAILURE:
raiserror(20628, 16, -1)
return 1

No comments:

Post a Comment

Total Pageviews