May 7, 2012

sp_MSadd_snapshot_agent (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_MSadd_snapshot_agent(nvarchar @name
, nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication
, int @publication_type
, bit @local_job
, int @freqtype
, int @freqinterval
, int @freqsubtype
, int @freqsubinterval
, int @freqrelativeinterval
, int @freqrecurrencefactor
, int @activestartdate
, int @activeenddate
, int @activestarttimeofday
, int @activeendtimeofday
, nvarchar @command
, bit @job_existing
, smallint @publisher_security_mode
, nvarchar @publisher_login
, nvarchar @publisher_password
, nvarchar @job_login
, nvarchar @job_password
, nvarchar @publisher_type
, nvarchar @internal)

MetaData:

 CREATE PROCEDURE sys.sp_MSadd_snapshot_agent  
(
@name nvarchar(100) = NULL,
@publisher sysname,
@publisher_db sysname,
@publication sysname,
@publication_type int = 0, -- 0 Transactional 1 Snapshot 2 Merge
@local_job bit,
@freqtype int = 4, -- 4== Daily
@freqinterval int = 1, -- Every day
@freqsubtype int = 4, -- Sub interval = Minute
@freqsubinterval int = 5, -- Every five minutes --
@freqrelativeinterval int = 1,
@freqrecurrencefactor int = 0,
@activestartdate int = 0, -- 12:00 am - 11:59 pm
@activeenddate int = 99991231, -- No start date
@activestarttimeofday int = 0,
@activeendtimeofday int = 235959, -- No end time
@command nvarchar(4000) = NULL,
@job_existing bit = 0, -- for 6x publisher
@snapshot_jobid binary(16) = NULL OUTPUT,
@publisher_security_mode smallint = NULL,
@publisher_login sysname = NULL,
@publisher_password nvarchar(524) = NULL,
@job_login nvarchar(257) = NULL,
@job_password sysname = NULL,
@publisher_type sysname = N'MSSQLSERVER',
@internal sysname = N'PRE-YUKON' -- Can be: 'PRE-YUKON', 'YUKON', 'BOTH'
)
AS
BEGIN
SET NOCOUNT ON

--
-- Declarations.
--
DECLARE @retcode int
DECLARE @publisher_id smallint
DECLARE @loc_publisher_db sysname
DECLARE @profile_id int
DECLARE @snapshot_type int
DECLARE @databasename sysname
DECLARE @agent_id int
DECLARE @category_name sysname
DECLARE @platform_nt binary
DECLARE @srvproduct nvarchar(128)
DECLARE @datasource nvarchar(4000)
DECLARE @snapshot_job_step_uid uniqueidentifier

-- Security Check: require sysadmin
IF (ISNULL(IS_SRVROLEMEMBER('sysadmin'),0) = 0)
BEGIN
RAISERROR(21089,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_MSadd_snapshot_agent', 'distribution')
return (1)
end

--
-- Initializations
--
select @platform_nt = 0x1

select @publisher_id = srvid,
@srvproduct = srvproduct,
@datasource = datasource
from master.dbo.sysservers
where UPPER(srvname) = UPPER(@publisher)

-- Always use integrated security for local connections
select @command = @command + ' -DistributorSecurityMode 1 '

-- For naming purposes, use @publisher instead of @publisher_db for HREPL
-- publishers that don't support publisher db notion
IF @publisher_type LIKE N'ORACLE%'
BEGIN
SELECT @loc_publisher_db = @publisher
END
ELSE
BEGIN
SELECT @loc_publisher_db = @publisher_db
END

IF NOT @publisher_type = N'MSSQLSERVER'
BEGIN
-- in the hetero case, if user specified integrated security
-- for the publisher security at sp_adddistpublisher time then
-- the provided snapshot agent job_login must be the same...
IF EXISTS(SELECT *
FROM msdb..MSdistpublishers
WHERE distribution_db = DB_NAME()
AND name = @publisher
AND login != @job_login
AND security_mode = 1
AND publisher_type = @publisher_type)
BEGIN
-- The job_login provided must match the publisher login specified when adding the distribution publisher (sp_adddistpublisher).
RAISERROR(22537, 16, -1)
RETURN 1
END
END

-- ONLY ALLOW THIS IN 8.0 or less CASE
IF @internal = N'PRE-YUKON'
BEGIN
-- if @name is not null and @job_existing = 1, the proc is from DMO scripting
-- check to see if the job is there or not, if not, reset @job_existing
-- value. This is for the case when the user generate the script at
-- the publisher but did not re-create repl jobs at the distributor.
if @local_job = 1 and @job_existing = 1 and @name is not null and
@name <> N''
begin
if not exists (select * from msdb.dbo.sysjobs_view
where name = @name
and UPPER(originating_server) = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName')))
and master_server = 0)
begin
set @job_existing = 0
set @name = null
end
end
END

BEGIN TRAN
SAVE TRAN tran_addsnapagent

-- Code for snapshot agent type in MSagent_profiles --
SELECT @snapshot_type = 1

-- Get the default profile ID for the snapshot agent type. If a third party publication
-- no profile is used.
if exists (select * from dbo.MSpublications where
publisher_id = @publisher_id and
publisher_db = @publisher_db and
publication = @publication and
thirdparty_flag = 1)
begin
set @profile_id = 0
end
else
begin
SELECT @profile_id = profile_id FROM msdb..MSagent_profiles WHERE
agent_type = @snapshot_type and
def_profile = 1
end

-- if the publisher sec info was not provided then default
-- the vals to the values provided durring sp_adddistpublisher...
-- this is only possible when called by SYSADMIN or 8.0 pub.
IF @publisher_security_mode is NULL
BEGIN
SELECT @publisher_security_mode = security_mode,
@publisher_login = login,
@publisher_password = password
FROM msdb.dbo.MSdistpublishers
WHERE UPPER(name) = UPPER(@publisher)
AND distribution_db = db_name()
AND publisher_type = @publisher_type
END
ELSE
BEGIN
-- if WINDOWS authentication then clear out the login/password
IF @publisher_security_mode = 1
BEGIN
SELECT @publisher_login = '',
@publisher_password = newid()
END

-- Encrypt the password before storing
EXEC @retcode = sys.sp_MSreplencrypt @publisher_password OUTPUT
IF @@ERROR <> 0 OR @retcode <> 0
GOTO UNDO
END

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_hostname is NULL)
begin
--
-- Insert row
--
INSERT INTO MSsnapshot_agents (name, publisher_id, publisher_db, publication, publication_type,
local_job, profile_id,
publisher_security_mode, publisher_login, publisher_password)
VALUES ('',@publisher_id, @publisher_db, @publication, @publication_type,
@local_job, @profile_id,
@publisher_security_mode, @publisher_login, @publisher_password)
IF @@ERROR <> 0
GOTO UNDO

set @agent_id = @@IDENTITY
end
else
begin
-- also retrieve the currently set
select @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_hostname is NULL

end

DECLARE @name_is_generated bit
SELECT @name_is_generated = 0
IF @name IS NULL OR @name = N''
BEGIN
SELECT @name_is_generated = 1
SELECT @name = CONVERT(nvarchar(28),@publisher) + '-' + CONVERT(nvarchar(28),@loc_publisher_db) + '-' +
CONVERT(nvarchar(28),@publication) + '-' + CONVERT(nvarchar, @agent_id)
END

-- If the generated name already exists, re-generate the name with a
-- guid appended
IF @name_is_generated = 1
BEGIN
IF EXISTS (SELECT * FROM msdb.dbo.sysjobs_view
WHERE name = @name
AND UPPER(originating_server) = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName')))
and master_server = 0)
BEGIN
SELECT @name = fn_repluniquename(newid(), @publisher, @loc_publisher_db, @publication, null)
END
END

-- Add Perfmon instance
dbcc addinstance ("SQL Replication Snapshot", @name)

IF @local_job = 1 and @job_existing = 0
BEGIN

DECLARE @nullchar nchar(20)
SELECT @nullchar = NULL

set @databasename = db_name()
-- Get Snapshot category name (assumes category_id = 15)
select @category_name = name FROM msdb.dbo.syscategories where category_id = 15

EXECUTE @retcode = dbo.sp_MSadd_repl_job
@name = @name,
@subsystem = 'Snapshot',
@server = @publisher,
@databasename = @databasename,
@enabled = 1,
@freqtype = @freqtype,
@freqinterval = @freqinterval,
@freqsubtype = @freqsubtype,
@freqsubinterval = @freqsubinterval,
@freqrelativeinterval = @freqrelativeinterval,
@freqrecurrencefactor = @freqrecurrencefactor,
@activestartdate = @activestartdate,
@activeenddate = @activeenddate,
@activestarttimeofday = @activestarttimeofday,
@activeendtimeofday = @activeendtimeofday,
@nextrundate = 0,
@nextruntime = 0,
@runpriority = 0,
@emailoperatorname = @nullchar,
@retryattempts = 10,
@retrydelay = 1,
@command = @command,
@loghistcompletionlevel = 0,
@emailcompletionlevel = 0,
@description = @nullchar,
@tagadditionalinfo = @nullchar,
@tagobjectid = 0,
@tagobjecttype = 0,
@category_name = @category_name,
@failure_detection = 1,
@agent_id = @agent_id,
@job_login = @job_login,
@job_password = @job_password,
@job_id = @snapshot_jobid OUTPUT,
@job_step_uid = @snapshot_job_step_uid OUTPUT

IF @@ERROR <> 0 or @retcode <> 0
GOTO UNDO
END

if @local_job = 1 and @job_existing = 1
begin
if @snapshot_jobid is null
begin
select @snapshot_jobid = sjv.job_id
from msdb.dbo.sysjobs_view as sjv
join msdb.dbo.sysjobsteps as sjs
on sjv.job_id = sjs.job_id
where sjv.name = @name
and sjv.master_server = 0
and UPPER(sjv.originating_server) = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName')))
and sjv.category_id = 15
and sjs.subsystem = N'Snapshot'
and sjs.database_name = db_name()

if @snapshot_jobid IS NULL
begin
-- Message from msdb.dbo.sp_verify_job_identifiers
RAISERROR(14262, -1, -1, 'Snapshot Job', @name)
GOTO UNDO
end
end
else
begin
if not exists (select * from msdb.dbo.sysjobs_view
where job_id = @snapshot_jobid
and UPPER(originating_server) = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName')))
and master_server = 0)
begin
-- Message from msdb.dbo.sp_verify_job_identifiers
RAISERROR(14262, -1, -1, 'Job', @name)
GOTO UNDO
end
end
end

-- retrieve the job step uid
if @snapshot_jobid is NOT NULL
and @snapshot_job_step_uid is NULL
begin
select @snapshot_job_step_uid = sjs.step_uid
from msdb.dbo.sysjobs_view as sjv
join msdb.dbo.sysjobsteps as sjs
on sjv.job_id = sjs.job_id
where sjv.job_id = @snapshot_jobid
and sjv.master_server = 0
and UPPER(sjv.originating_server) = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName')))
and sjv.category_id = 15
and sjs.subsystem = N'Snapshot'
and sjs.database_name = db_name()
end

-- Moved up
-- Get the job id if it already exists
if @local_job = 1 and @job_existing = 1
begin
select @snapshot_jobid = job_id from msdb.dbo.sysjobs_view
where job_id = @snapshot_jobid
and UPPER(originating_server) = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName')))
and master_server = 0
if @snapshot_jobid IS NULL
begin
-- Message from msdb.dbo.sp_verify_job_identifiers
RAISERROR(14262, -1, -1, 'Job', @name)
GOTO UNDO
end
end
--

-- Generate a job GUID for remote agents. This will be used by the UI to uniquely
-- identify rows returned by the enums
if @local_job = 0
begin
-- Third party publication will pass in snapshot agent name which is created as
-- a SQLServerAgent job.
if @name is not null
select @snapshot_jobid = job_id from msdb.dbo.sysjobs_view where
name = @name
if @snapshot_jobid is null
set @snapshot_jobid = newid()
-- Reset @local_job to 1 so that repl monitor can start the job.
-- In sp_MSdrop_snapshot_agent, we will not drop the job if the publication
-- is from third party.
else
set @local_job = 1
end

-- Caution: @local job might be changed from the passed in value.
UPDATE MSsnapshot_agents
SET name = @name,
job_id = @snapshot_jobid,
-- Update the following fields because the row maybe added before this sp call
-- by sp_MSadd_publication.
publication_type = @publication_type,
local_job = @local_job,
profile_id = @profile_id,
job_step_uid = @snapshot_job_step_uid,
publisher_security_mode = @publisher_security_mode,
publisher_login = @publisher_login,
publisher_password = @publisher_password
WHERE id = @agent_id

IF @@ERROR <> 0
GOTO UNDO

COMMIT TRAN tran_addsnapagent

RETURN(0)

UNDO:
ROLLBACK TRAN tran_addsnapagent
COMMIT TRAN

return(1)
END

No comments:

Post a Comment

Total Pageviews