May 8, 2012

sp_MSchangedynamicsnapshotjobatdistributor (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_MSchangedynamicsnapshotjobatdistributor(nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication
, nvarchar @dynamic_filter_login
, nvarchar @dynamic_filter_hostname
, int @frequency_type
, int @frequency_interval
, int @frequency_subday
, int @frequency_subday_interval
, int @frequency_relative_interval
, int @frequency_recurrence_factor
, int @active_start_date
, int @active_end_date
, int @active_start_time_of_day
, int @active_end_time_of_day
, nvarchar @job_login
, nvarchar @job_password)

MetaData:

 --   
-- Name: sp_MSchangedynamicsnapshotjobatdistributor
--
-- Description: This function is called by sp_changeddynamicsnapshot_job
-- at the publisher to change the schedule of a dynamic snapshot job
-- at the distributor.
--
-- 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_MSchangedynamicsnapshotjobatdistributor
(
@publisher sysname,
@publisher_db sysname,
@publication sysname,
@dynamic_filter_login sysname,
@dynamic_filter_hostname sysname,

-- Scheduling information
@frequency_type int = NULL,
@frequency_interval int = NULL,
@frequency_subday int = NULL,
@frequency_subday_interval int = NULL,
@frequency_relative_interval int = NULL,
@frequency_recurrence_factor int = NULL,
@active_start_date int = NULL,
@active_end_date int = NULL,
@active_start_time_of_day int = NULL,
@active_end_time_of_day int = NULL,

-- Job Login information
@job_login nvarchar(257) = NULL,
@job_password sysname = NULL
)
as
begin
set nocount on

declare @retcode int
declare @agent_id int
declare @job_id uniqueidentifier
declare @job_step_uid uniqueidentifier
declare @publisher_id int
declare @publication_type int

--
-- security check
-- only db_owner can execute this
--
if (is_srvrolemember('sysadmin') != 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_MSchangedynamicsnapshotjobatdistributor', 'distribution')
return (1)
end

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

select @retcode = 0
select @agent_id = null

SELECT @publisher_id = srvid
FROM master.dbo.sysservers
WHERE UPPER(srvname) = UPPER(@publisher)
IF @publisher_id IS NULL
BEGIN
RAISERROR(21169, 16, -1, @publisher, @@SERVERNAME, @publisher)
END

-- Get the publication details from the agent for regular snapshot
select @agent_id = id,
@job_id = job_id,
@job_step_uid = job_step_uid,
@publication_type = publication_type
from MSsnapshot_agents
where publisher_id = @publisher_id and
publication = @publication and
publisher_db = @publisher_db 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 @agent_id is null or @job_id is NULL
begin
raiserror(21325, 11, -1)
return 1
end

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

-- Change the job
exec @retcode = sys.sp_MSchange_repl_job @id = @job_id,
@step_uid = @job_step_uid,
@name = NULL,
@frequency_type = @frequency_type,
@frequency_interval = @frequency_interval,
@frequency_subday = @frequency_subday,
@frequency_subday_interval = @frequency_subday_interval,
@frequency_relative_interval = @frequency_relative_interval,
@frequency_recurrence_factor = @frequency_recurrence_factor,
@active_start_date = @active_start_date,
@active_end_date = @active_end_date,
@active_start_time_of_day = @active_start_time_of_day,
@active_end_time_of_day = @active_end_time_of_day,
@login = @job_login,
@password = @job_password

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

RETURN(0)

UNDO:
return(1)
end

No comments:

Post a Comment

Total Pageviews