May 7, 2012

sp_MSadddynamicsnapshotjobatdistributor (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_MSadddynamicsnapshotjobatdistributor(uniqueidentifier @regular_snapshot_jobid
, nvarchar @dynamic_filter_login
, nvarchar @dynamic_filter_hostname
, nvarchar @dynamic_snapshot_location
, int @freqtype
, int @freqinterval
, int @freqsubtype
, int @freqsubinterval
, int @freqrelativeinterval
, int @freqrecurrencefactor
, int @activestartdate
, int @activeenddate
, int @activestarttimeofday
, int @activeendtimeofday
, int @partition_id)

MetaData:

 --   
-- Name: sp_MSadddynamicsnapshotjobatdistributor
--
-- Description: This function is called by sp_MSaddmergedynamicsnapshotjob
-- at the publisher to set up a dynamic snapshot job in msdb
-- at the distributor.
-- Notes: This procedure will only perform implicit checking for scheduling
-- parameters as most parameters are expected to have been checked
-- in sp_MSaddmergedynamicsnapshotjob
--
-- Parameters: @regular_snapshot_jobid uniqueidentifier (mandatory)
-- @dynamic_filter_login sysname (optional, default null)
-- @dynamic_filter_hostname sysname (optional, default null)
-- @dynamic_snapshot_location nvarchar(255) (mandatory)
-- @dynamic_snapshot_jobid uniqueidentifier (mandatory)
-- Scheduling information:
-- @freqtype int (optional, default 4 == Daily)
-- @freqinterval int (optional, default 1 == Every day)
-- @freqsubtype int (optional, default 4 (Sub interval = Minute))
-- @freqsubinterval int (optional, default 5 == Every five minutes)
-- @freqrelativeinterval int (optional, default 1)
-- @freqrecurrencefactor int (optional, default 0)
-- @activestartdate int (optional, default 0 == Today)
-- @activeenddate int (optional, default 99991231)
-- @activestarttimeofday int (optional, default 0 == Now)
-- @activeendtimeofday int (optional, default 235959)
-- @partition_id int (optional, default null)
--
-- Returns: 0 - succeeded
-- 1 - failed
--
-- Security: Only members of the 'sysadmin' server role and members of the
-- 'db_owner' database role at the distributor can call this
-- procedure. This procedure is intended to be called through
-- the distributor_admin remote login in the case where
-- the distributor is a different machine from the publisher.
--
create procedure sys.sp_MSadddynamicsnapshotjobatdistributor
(
@regular_snapshot_jobid uniqueidentifier,
@dynamic_filter_login sysname = null,
@dynamic_filter_hostname sysname = null,
@dynamic_snapshot_location nvarchar(255),
@dynamic_snapshot_jobname nvarchar(100) output,
@dynamic_snapshot_jobid uniqueidentifier output,
@dynamic_snapshot_job_step_uid uniqueidentifier output,

-- Scheduling information
@freqtype INT = 2, -- 2 means OnDemand
@freqinterval INT = 1,
@freqsubtype INT = 1,
@freqsubinterval INT = 1,
@freqrelativeinterval INT = 1,
@freqrecurrencefactor INT = 1,
@activestartdate INT = 0,
@activeenddate INT = 0,
@activestarttimeofday INT = 0,
@activeendtimeofday INT = 0,
@dynamic_snapshot_agent_id int = NULL output,
@partition_id INT = NULL
)
as
begin
set nocount on

declare @retcode int
declare @agent_command_line nvarchar(4000)
declare @agent_id int
declare @db_name sysname
declare @category_name sysname
declare @nullchar nchar(20)
declare @publisher_id int
declare @publication sysname
declare @publisher_db sysname
declare @publication_type int
declare @local_job bit
declare @profile_id int
declare @dynamicfilterloginparam nvarchar(50)
declare @dynamicfilterhostnameparam nvarchar(50)
declare @dynamicsnapshotlocationparam nvarchar(50)
declare @proxy_id int
declare @publisher_security_mode int
declare @publisher_login sysname
declare @publisher_password nvarchar(524)
declare @next_agent_id int

--
-- security check
-- only db_owner can execute this
--
if (is_member ('db_owner') != 1)
begin
raiserror(14260, 16, -1)
return (1)
end
--
-- security check
-- Has to be executed from distribution database
--
if (sys.fn_MSrepl_isdistdb (db_name()) != 1)
begin
raiserror(21482, 16, -1, 'sp_MSadddynamicsnapshotjobatdistributor', 'distribution')
return (1)
end

select @retcode = 0
select @agent_command_line = null
select @agent_id = null
select @db_name = db_name()
select @category_name = name
from msdb.dbo.syscategories
where category_id = 15
select @nullchar = null

select @dynamicfilterloginparam = N' -DynamicFilterLogin '
select @dynamicfilterhostnameparam = N' -DynamicFilterHostName '
select @dynamicsnapshotlocationparam = N' -DynamicSnapshotLocation '

-- Get the regular snapshot agent command line
select @agent_command_line = command,
@proxy_id = proxy_id
from msdb.dbo.sysjobsteps
where job_id = @regular_snapshot_jobid
and subsystem = N'Snapshot'
and step_id = 2

if @agent_command_line is null
begin
raiserror(21319, 11, -1)
return 1
end

-- Get the publication details from the agent for regular snapshot
select @agent_id = id,
@publisher_id = publisher_id,
@publication = publication,
@publisher_db = publisher_db,
@publication_type = publication_type,
@local_job = local_job,
@profile_id = profile_id,
@publisher_security_mode = publisher_security_mode,
@publisher_login = publisher_login,
@publisher_password = publisher_password
from MSsnapshot_agents
where job_id = @regular_snapshot_jobid

if @agent_id is null
begin
raiserror(21325, 11, -1)
return 1
end

if @local_job = 0
begin
raiserror(21325, 11, -1)
return 1
end

if @publication_type <> 2
begin
raiserror(20654, 16, -1)
return 1
end

if @dynamic_filter_login is NULL and @dynamic_filter_hostname is NULL
begin
raiserror(20653, 16, -1)
return 1
end


BEGIN TRAN

-- need to do this so that ident_current does not change after the read and insert
select @next_agent_id = IDENT_CURRENT('MSsnapshot_agents') + 1

if @dynamic_snapshot_jobname is null or @dynamic_snapshot_jobname = N''
begin
-- Get the job name of the regular snapshot job. This is going to be used
-- to derive a uniquefied job name for the dynamic snapshot job
select @dynamic_snapshot_jobname = name
from msdb.dbo.sysjobs
where job_id = @regular_snapshot_jobid

-- select @dynamic_snapshot_jobname = N'dyn_' + left(@dynamic_snapshot_jobname,88) + convert(nvarchar(36), newid())
select @dynamic_snapshot_jobname = N'dyn_' + left(@dynamic_snapshot_jobname,80) + '_' +
left(isnull(@dynamic_filter_login,''), 16) + '_' +
left(isnull(@dynamic_filter_hostname,''), 16) + '_' +
convert(nvarchar(8), @next_agent_id)
end

-- the dynamic snapshot job should have different agent id other than the regular snapshot job
-- so create an agent id if one does not exist
if not exists (select * from MSsnapshot_agents where
publisher_id = @publisher_id and
publisher_db = @publisher_db and
publication = @publication and
((@dynamic_filter_login is NULL and dynamic_filter_login is NULL) or dynamic_filter_login = @dynamic_filter_login) and
((@dynamic_filter_hostname is NULL and dynamic_filter_hostname is NULL) or dynamic_filter_hostname = @dynamic_filter_hostname))
begin
--
-- Insert row
--
INSERT INTO MSsnapshot_agents (name, publisher_id, publisher_db, publication, publication_type,
local_job, profile_id, dynamic_filter_login, dynamic_filter_hostname,
publisher_security_mode, publisher_login, publisher_password)
VALUES (@dynamic_snapshot_jobname,@publisher_id, @publisher_db, @publication, @publication_type,
@local_job, @profile_id, @dynamic_filter_login, @dynamic_filter_hostname,
@publisher_security_mode, @publisher_login, @publisher_password)
IF @@ERROR <> 0
goto UNDO

set @dynamic_snapshot_agent_id = @@IDENTITY
end
else
select @dynamic_snapshot_agent_id = id from MSsnapshot_agents where
publisher_id = @publisher_id and
publisher_db = @publisher_db and
publication = @publication and
((@dynamic_filter_login is NULL and dynamic_filter_login is NULL) or dynamic_filter_login = @dynamic_filter_login) and
((@dynamic_filter_hostname is NULL and dynamic_filter_hostname is NULL) or dynamic_filter_hostname = @dynamic_filter_hostname)

if @dynamic_filter_login is not null and @dynamic_filter_login <> N''
begin
select @agent_command_line = @agent_command_line + @dynamicfilterloginparam + N'[' + rtrim(@dynamic_filter_login) + N']'
end

if @dynamic_filter_hostname is not null and @dynamic_filter_hostname <> N''
begin
select @agent_command_line = @agent_command_line + @dynamicfilterhostnameparam + N'[' + rtrim(@dynamic_filter_hostname) + N']'
end

-- @dynamic_snapshot_location is assumed to be non-empty
select @agent_command_line = @agent_command_line + @dynamicsnapshotlocationparam + N'[' + @dynamic_snapshot_location + N']'


-- Add partition id if it is not null
if @partition_id is not null
begin
select @agent_command_line = @agent_command_line + N' -PartitionId ' + convert(nvarchar(10), @partition_id)
end

-- Add the job
exec @retcode = dbo.sp_MSadd_repl_job
@name = @dynamic_snapshot_jobname,
@subsystem = 'Snapshot',
@server = @@servername,
@databasename = @db_name,
@enabled = 1,
@freqtype = @freqtype,
@freqinterval = @freqinterval,
@freqsubtype = @freqsubtype,
@freqsubinterval = @freqsubinterval,
@freqrecurrencefactor = @freqrecurrencefactor,
@activestartdate = @activestartdate,
@activeenddate = @activeenddate,
@activestarttimeofday = @activestarttimeofday,
@activeendtimeofday = @activeendtimeofday,
@nextrundate = 0,
@nextruntime = 0,
@runpriority = 0,
@emailoperatorname = @nullchar,
@retryattempts = 10,
@retrydelay = 1,
@command = @agent_command_line,
@loghistcompletionlevel = 0,
@emailcompletionlevel = 0,
@description = @nullchar,
@tagobjectid = 0,
@tagobjecttype = 0,
@category_name = @category_name,
@failure_detection = 1,
@agent_id = @dynamic_snapshot_agent_id,
@proxy_id = @proxy_id,
@job_id = @dynamic_snapshot_jobid OUTPUT,
@job_step_uid = @dynamic_snapshot_job_step_uid OUTPUT

if @retcode <> 0 or @@error <> 0
goto UNDO

update MSsnapshot_agents
set job_id = @dynamic_snapshot_jobid,
name = @dynamic_snapshot_jobname,
local_job = @local_job,
job_step_uid = @dynamic_snapshot_job_step_uid
where id = @dynamic_snapshot_agent_id
if @@error <> 0
goto UNDO

COMMIT TRAN

RETURN(0)

UNDO:
if @@TRANCOUNT = 1
ROLLBACK TRAN
else
COMMIT TRAN
return(1)
end

No comments:

Post a Comment

Total Pageviews