June 4, 2012

sp_MSupdate_subscriber_schedule (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_MSupdate_subscriber_schedule(nvarchar @publisher
, nvarchar @subscriber
, tinyint @agent_type
, int @frequency_type
, int @frequency_interval
, int @frequency_relative_interval
, int @frequency_recurrence_factor
, int @frequency_subday
, int @frequency_subday_interval
, int @active_start_time_of_day
, int @active_end_time_of_day
, int @active_start_date
, int @active_end_date)

MetaData:

 CREATE PROCEDURE sys.sp_MSupdate_subscriber_schedule  
(
@publisher sysname,
@subscriber sysname,
@agent_type tinyint = NULL,
@frequency_type int = NULL,
@frequency_interval int = NULL,
@frequency_relative_interval int = NULL,
@frequency_recurrence_factor int = NULL,
@frequency_subday int = NULL,
@frequency_subday_interval int = NULL,
@active_start_time_of_day int = NULL,
@active_end_time_of_day int = NULL,
@active_start_date int = NULL,
@active_end_date int = NULL
)
AS
begin
set nocount on

declare @cmd1 nvarchar (255)
declare @retcode int

--
-- security check
-- only db_owner can execute this
--
if (is_member ('db_owner') != 1)
begin
raiserror(14260, 16, -1)
return (1)
end

begin transaction
save transaction update_subscriber_schedule

-- Check if subscriber exists --
if not exists (select * from MSsubscriber_info
where UPPER(publisher) = UPPER(@publisher) and UPPER(subscriber) = UPPER(@subscriber))
goto FAILED

if not exists (select * from MSsubscriber_schedule
where UPPER(publisher) = UPPER(@publisher) and UPPER(subscriber) = UPPER(@subscriber) and agent_type = @agent_type)
goto FAILED


if @frequency_type is not NULL
update MSsubscriber_schedule set frequency_type = @frequency_type
where UPPER(publisher) = UPPER(@publisher) and UPPER(subscriber) = UPPER(@subscriber) and agent_type = @agent_type
if @@error <> 0
goto FAILED

if @frequency_interval is not NULL
update MSsubscriber_schedule set frequency_interval = @frequency_interval
where UPPER(publisher) = UPPER(@publisher) and UPPER(subscriber) = UPPER(@subscriber) and agent_type = @agent_type
if @@error <> 0
goto FAILED

if @frequency_relative_interval is not NULL
update MSsubscriber_schedule set frequency_relative_interval = @frequency_relative_interval
where UPPER(publisher) = UPPER(@publisher) and UPPER(subscriber) = UPPER(@subscriber) and agent_type = @agent_type
if @@error <> 0
goto FAILED

if @frequency_recurrence_factor is not NULL
update MSsubscriber_schedule set frequency_recurrence_factor = @frequency_recurrence_factor
where UPPER(publisher) = UPPER(@publisher) and UPPER(subscriber) = UPPER(@subscriber) and agent_type = @agent_type
if @@error <> 0
goto FAILED

if @frequency_subday is not NULL
update MSsubscriber_schedule set frequency_subday = @frequency_subday
where UPPER(publisher) = UPPER(@publisher) and UPPER(subscriber) = UPPER(@subscriber) and agent_type = @agent_type
if @@error <> 0
goto FAILED

if @frequency_subday_interval is not NULL
update MSsubscriber_schedule set frequency_subday_interval = @frequency_subday_interval
where UPPER(publisher) = UPPER(@publisher) and UPPER(subscriber) = UPPER(@subscriber) and agent_type = @agent_type
if @@error <> 0
goto FAILED

if @active_start_time_of_day is not NULL
update MSsubscriber_schedule set active_start_time_of_day = @active_start_time_of_day
where UPPER(publisher) = UPPER(@publisher) and UPPER(subscriber) = UPPER(@subscriber) and agent_type = @agent_type
if @@error <> 0
goto FAILED

if @active_end_time_of_day is not NULL
update MSsubscriber_schedule set active_end_time_of_day = @active_end_time_of_day
where UPPER(publisher) = UPPER(@publisher) and UPPER(subscriber) = UPPER(@subscriber) and agent_type = @agent_type
if @@error <> 0
goto FAILED

if @active_start_date is not NULL
update MSsubscriber_schedule set active_start_date = @active_start_date
where UPPER(publisher) = UPPER(@publisher) and UPPER(subscriber) = UPPER(@subscriber) and agent_type = @agent_type
if @@error <> 0
goto FAILED

if @active_end_date is not NULL
update MSsubscriber_schedule set active_end_date = @active_end_date
where UPPER(publisher) = UPPER(@publisher) and UPPER(subscriber) = UPPER(@subscriber) and agent_type = @agent_type
if @@error <> 0
goto FAILED

commit transaction
return (0)

FAILED:
if @@trancount > 0
begin
rollback transaction update_subscriber_schedule
commit transaction -- to finish off the tran we started in this proc (though
-- work was rolled back to savepoint)
end
return (1)
end

No comments:

Post a Comment

Total Pageviews