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_MSchange_snapshot_agent_properties(nvarchar @publisher, nvarchar @publisher_db
, nvarchar @publication
, 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 @snapshot_job_name
, int @publisher_security_mode
, nvarchar @publisher_login
, nvarchar @publisher_password
, nvarchar @job_login
, nvarchar @job_password
, nvarchar @publisher_type)
MetaData:
create procedure sys.sp_MSchange_snapshot_agent_properties ( @publisher sysname, @publisher_db sysname, @publication sysname, @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 int, @snapshot_job_name nvarchar(100), @publisher_security_mode int, @publisher_login sysname, @publisher_password nvarchar(524), @job_login nvarchar(257), @job_password sysname, @publisher_type sysname ) as begin declare @retcode bit, @publisher_id int, @agent_id int, @agent_exists bit, @job_id uniqueidentifier, @job_step_uid uniqueidentifier, @pubsecmode int, @proxy_id int -- security: Has to be executed by SA of dist db if is_srvrolemember('sysadmin') != 1 begin -- You do not have the required permissions to complete the operation. raiserror (20604, 16, -1) return 1 end select @proxy_id = NULL begin transaction tran_sp_MSchange_snapshot save transaction tran_sp_MSchange_snapshot -- retrieve the publisher id select @publisher_id = server_id from sys.servers where upper(name) = upper(@publisher) if @publisher_id is null begin -- Publisher @publisher does not exist. raiserror(21618, 16, -1, @publisher) goto FAILED end if @publisher_security_mode is not NULL or @publisher_login is not NULL or @publisher_password is not NULL 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 FAILED update MSsnapshot_agents set publisher_security_mode = isnull(@publisher_security_mode, publisher_security_mode), publisher_login = isnull(@publisher_login, publisher_login), publisher_password = isnull(@publisher_password, publisher_password) where publisher_id = @publisher_id and publisher_db = @publisher_db and publication = @publication if @@error <> 0 goto FAILED end if @job_login is not NULL or @job_password is not NULL or @snapshot_job_name is not NULL or @frequency_type is not NULL or @frequency_interval is not NULL or @frequency_subday is not NULL or @frequency_subday_interval is not NULL or @frequency_relative_interval is not NULL or @frequency_recurrence_factor is not NULL or @active_start_date is not NULL or @active_end_date is not NULL or @active_start_time_of_day is not NULL or @active_end_time_of_day is not NULL begin -- HETERO check only if @publisher_type != N'MSSQLSERVER' begin -- we can only allow @job_login change for Hetero when -- the publisher_security_mode is standard security... select @pubsecmode = security_mode from msdb..MSdistpublishers where name = @publisher and distribution_db = db_name() if @pubsecmode is not NULL AND @pubsecmode != 0 AND @job_login is not NULL begin -- "@job_login can only be specified/changed for heterogeneous publications when the publisher security_mode (for sp_adddistpublisher) is set to 0." RAISERROR(21842, 16, -1, '@job_login', 'the publisher security_mode (for sp_adddistpublisher)', '0') goto FAILED end end DECLARE #cursorSnapAgents CURSOR LOCAL FAST_FORWARD FOR SELECT mssa.name, CAST(mssa.job_id as uniqueidentifier), mssa.job_step_uid FROM msdb.dbo.sysjobs_view sjv JOIN MSsnapshot_agents mssa ON sjv.job_id = CAST(mssa.job_id as uniqueidentifier) JOIN msdb.dbo.sysjobsteps sjs ON sjv.job_id = sjs.job_id AND mssa.job_step_uid = sjs.step_uid WHERE mssa.publisher_id = @publisher_id AND mssa.publisher_db = @publisher_db AND mssa.publication = @publication FOR READ ONLY OPEN #cursorSnapAgents FETCH #cursorSnapAgents INTO @snapshot_job_name, @job_id, @job_step_uid WHILE @@FETCH_STATUS <> -1 BEGIN IF @job_id IS NOT NULL AND @job_step_uid IS NULL BEGIN SELECT @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 = @job_id 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 exec @retcode = sys.sp_MSchange_repl_job @id = @job_id, @step_uid = @job_step_uid, @name = @snapshot_job_name, @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, @proxy_id = @proxy_id OUTPUT if @@error <> 0 or @retcode <> 0 goto FAILED FETCH #cursorSnapAgents INTO @snapshot_job_name, @job_id, @job_step_uid END CLOSE #cursorSnapAgents DEALLOCATE #cursorSnapAgents end commit transaction tran_sp_MSchange_snapshot return 0 FAILED: rollback transaction tran_sp_MSchange_snapshot commit transaction return 1 end
No comments:
Post a Comment