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_merge_agent(nvarchar @name, nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication
, nvarchar @subscriber
, nvarchar @subscriber_db
, bit @local_job
, 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
, nvarchar @optional_command_line
, bit @offloadagent
, nvarchar @offloadserver
, int @subscription_type
, nvarchar @hostname
, smallint @subscriber_security_mode
, nvarchar @subscriber_login
, nvarchar @subscriber_password
, smallint @publisher_security_mode
, nvarchar @publisher_login
, nvarchar @publisher_password
, nvarchar @job_login
, nvarchar @job_password
, nvarchar @internal
, int @publisher_engine_edition)
MetaData:
CREATE PROCEDURE sys.sp_MSadd_merge_agent ( -- not null if from scripting @name sysname = NULL, @publisher sysname, -- Publisher server -- @publisher_db sysname, -- Publisher database -- @publication sysname, -- Publication name -- @subscriber sysname, -- Subscriber server -- @subscriber_db sysname, -- Subscription database -- @local_job bit, @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, @optional_command_line nvarchar(255) = '', -- Optional command line arguments -- @merge_jobid binary(16) = NULL OUTPUT, -- Agent offload @offloadagent bit = 0, @offloadserver sysname = NULL, @subscription_type int = 0, -- 0 = push, 1 = pull @hostname sysname = NULL, -- used for subscription based security @subscriber_security_mode smallint = NULL, @subscriber_login sysname = NULL, @subscriber_password nvarchar(524) = NULL, @publisher_security_mode smallint = NULL, @publisher_login sysname = NULL, @publisher_password nvarchar(524) = NULL, -- used for jobstep level proxy account @job_login nvarchar(257) = NULL, @job_password sysname = NULL, @internal sysname = N'PRE-YUKON', -- Can be: 'PRE-YUKON', 'YUKON ADD SUB', 'YUKON ADD AGENT' @publisher_engine_edition int = NULL ) AS begin SET NOCOUNT ON -- -- Declarations. -- declare @retcode int DECLARE @publisher_id smallint DECLARE @subscriber_id smallint DECLARE @profile_id int DECLARE @merge_type int DECLARE @command nvarchar(4000) DECLARE @subscriber_datasource_type int -- 0 SQL Server, 1 ODBC, 2 Jet, 3 OLEDB -- DECLARE @distributor sysname DECLARE @database sysname DECLARE @agent_id int DECLARE @category_name sysname DECLARE @dsn_subscriber tinyint DECLARE @jet_subscriber tinyint DECLARE @oledb_subscriber tinyint DECLARE @exchange_subscriber tinyint DECLARE @oracle_subscriber tinyint DECLARE @db2universal_subscriber tinyint DECLARE @platform_nt binary DECLARE @provider_name sysname DECLARE @merge_job_step_uid uniqueidentifier DECLARE @subscriber_encrypted_password nvarchar(524) DECLARE @publisher_encrypted_password nvarchar(524) set @distributor = @@SERVERNAME set @dsn_subscriber = 1 -- Const: subscriber type 'dsn' -- set @jet_subscriber = 2 set @oledb_subscriber = 3 set @exchange_subscriber = 4 set @oracle_subscriber = 5 set @db2universal_subscriber = 6 set @platform_nt = 0x1 set @merge_job_step_uid = NULL -- Security Check: require sysadmin IF (ISNULL(IS_SRVROLEMEMBER('sysadmin'),0) = 0) BEGIN RAISERROR(21089,16,-1) RETURN 1 END IF (sys.fn_MSrepl_isdistdb (DB_NAME()) != 1) BEGIN -- "sp_MSadd_merge_agent can only be executed in the distribution database." RAISERROR(21482, 16, -1, 'sp_MSadd_merge_agent', 'distribution') RETURN 1 END IF @offloadagent IS NOT NULL AND @offloadagent != 0 BEGIN -- "Parameter '@offloadagent' is no longer supported." RAISERROR(21698, 16, -1, '@offloadagent') RETURN 1 END IF ISNULL(@offloadserver, N'') != N'' BEGIN -- "Parameter '@offloadserver' is no longer supported." RAISERROR(21698, 16, -1, '@offloadserver') RETURN 1 END -- Set null @optional_command_line to empty string to avoid string concat problem SELECT @optional_command_line = ISNULL(N' ' + LTRIM( RTRIM(@optional_command_line) ) + N' ', N'') -- -- Initializations -- -- Get subscriber info select @subscriber_id = srvid from master.dbo.sysservers where UPPER(srvname) = UPPER(@subscriber) select @publisher_id = srvid from master.dbo.sysservers where UPPER(srvname) = UPPER(@publisher) select @subscriber_datasource_type = type from MSsubscriber_info where UPPER(publisher) = UPPER(@publisher) and UPPER(subscriber) = UPPER(@subscriber) -- -- Jet and Oracle subscribers are actually added to MSsubscriber_info as OLE DB subscribers, -- since they can be used in transactional replication also. -- Map the type to Jet or Oracle based on OLE DB provider name. -- if (@subscriber_datasource_type = @oledb_subscriber) BEGIN select @provider_name = providername from master.dbo.sysservers where UPPER(srvname) = UPPER(@subscriber) if (upper(@provider_name) = 'MICROSOFT.JET.OLEDB.4.0') select @subscriber_datasource_type = @jet_subscriber else if (upper(@provider_name) = 'MSDAORA') select @subscriber_datasource_type = @oracle_subscriber else if (upper(@provider_name) = 'DB2OLEDB') select @subscriber_datasource_type = @db2universal_subscriber END if (@subscriber_datasource_type IS NULL) select @subscriber_datasource_type = 0 -- ONLY ALLOW THIS IN 8.0 or less CASE IF @internal = N'PRE-YUKON' BEGIN -- if @name is not null, the proc is from DMO scripting -- check to see if the job is there are not, if not, reset @job_existing and -- @name values. 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 @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 @name = null end end END BEGIN TRANSACTION tr_add_merge_agent SAVE TRANSACTION tr_add_merge_agent -- Code for merge agent type in MSagent_profiles -- SELECT @merge_type = 4 SELECT @profile_id = profile_id FROM msdb..MSagent_profiles WHERE agent_type = @merge_type AND def_profile = 1 IF @profile_id IS NULL RETURN (1) IF @internal = N'PRE-YUKON' OR @internal = N'YUKON ADD AGENT' BEGIN -- if the subscriber sec info was not provided then default -- the vals to the values provided durring sp_addsubscriber... -- this is only possible when called by SYSADMIN or 8.0 pub. IF @subscriber_security_mode is NULL BEGIN -- Get default task parameter values from MSsubscriber_info SELECT @subscriber_security_mode = security_mode, @subscriber_login = login, @subscriber_password = password FROM MSsubscriber_info WHERE UPPER(publisher) = UPPER(@publisher) AND UPPER(subscriber) = UPPER(@subscriber) IF @subscriber_security_mode IS NULL BEGIN SELECT @subscriber_security_mode = 1, @subscriber_login = '', @subscriber_password = newid() -- Encrypt the password before storing EXEC @retcode = sys.sp_MSreplencrypt @subscriber_password OUTPUT IF @@ERROR <> 0 OR @retcode <> 0 GOTO UNDO END END ELSE BEGIN IF @subscriber_security_mode = 1 BEGIN SELECT @subscriber_login = N'', @subscriber_password = newid() END EXEC @retcode = sys.sp_MSreplencrypt @subscriber_password OUTPUT IF @@error <> 0 or @retcode <> 0 GOTO UNDO 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 -- if the publisher security mode was not provided then -- we will fall back to the backcmpt form which is the -- value found in the msdb..MSdistpublishers table... select @publisher_security_mode = security_mode, @publisher_login = login, @publisher_password = password from msdb..MSdistpublishers where upper(name) = upper(@publisher) and distribution_db = db_name() END ELSE BEGIN IF @publisher_security_mode = 1 BEGIN SELECT @publisher_login = N'', @publisher_password = newid() END EXEC @retcode = sys.sp_MSreplencrypt @publisher_password OUTPUT IF @@error <> 0 or @retcode <> 0 GOTO UNDO END END IF @internal = N'PRE-YUKON' OR @internal = N'YUKON ADD SUB' BEGIN -- Try to drop it first EXEC sys.sp_MSdrop_merge_agent @publisher = @publisher, @publisher_db = @publisher_db, @publication = @publication, @subscriber = @subscriber, @subscriber_db = @subscriber_db IF @@ERROR <> 0 GOTO UNDO IF @internal = N'YUKON ADD SUB' BEGIN -- When calling it from internal = 0 we know that this is -- a dummy row until the actual value is set on addpushagent SELECT @subscriber_security_mode = 1, @subscriber_login = N'', @subscriber_password = newid(), @publisher_security_mode = 1, @publisher_login = N'', @publisher_password = newid() EXEC @retcode = sys.sp_MSreplencrypt @subscriber_password OUTPUT IF @@error <> 0 or @retcode <> 0 GOTO UNDO EXEC @retcode = sys.sp_MSreplencrypt @publisher_password OUTPUT IF @@error <> 0 or @retcode <> 0 GOTO UNDO END exec @retcode = sys.sp_MScheck_subscription_count_internal @mode = 1, @publisher = @publisher, @publisher_engine_edition = @publisher_engine_edition, @about_to_insert_new_subscription=1 if @@error <> 0 or @retcode <> 0 goto UNDO -- -- Insert row -- INSERT INTO dbo.MSmerge_agents (name, publisher_id, publisher_db, publication, subscriber_id, subscriber_db, local_job, profile_id, subscriber_security_mode, subscriber_login, subscriber_password, subscriber_name, publisher_security_mode, publisher_login, publisher_password) VALUES ('',@publisher_id, @publisher_db, @publication, @subscriber_id, @subscriber_db, @local_job, @profile_id, @subscriber_security_mode, @subscriber_login, @subscriber_password, @subscriber, @publisher_security_mode, @publisher_login, @publisher_password) IF @@ERROR <> 0 GOTO UNDO SELECT @agent_id = @@IDENTITY END ELSE IF @internal = N'YUKON ADD AGENT' BEGIN SELECT @agent_id = id FROM MSmerge_agents WHERE publisher_id = @publisher_id AND publisher_db = @publisher_db AND publication = @publication AND upper(subscriber_name) = upper(@subscriber) AND subscriber_db = @subscriber_db UPDATE MSmerge_agents SET subscriber_security_mode = @subscriber_security_mode, subscriber_login = @subscriber_login, subscriber_password = @subscriber_password, publisher_security_mode = @publisher_security_mode, publisher_login = @publisher_login, publisher_password = @publisher_password WHERE @agent_id = id END declare @job_existing bit IF @name IS NULL OR @name = N'' begin SELECT @name = CONVERT(nvarchar(21),@publisher ) + '-' + CONVERT(nvarchar(21),@publisher_db) + '-' + CONVERT(nvarchar(21),@publication) + '-' + CONVERT(nvarchar(21),@subscriber) + '-' + CONVERT(nvarchar, @agent_id) select @job_existing = 0 end else select @job_existing = 1 -- If creating a new job and the generated name already exists, re-generate the name with a -- guid appended IF @job_existing = 0 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, @publisher_db, @publication, @subscriber) END END IF @internal = N'PRE-YUKON' OR @internal = N'YUKON ADD AGENT' BEGIN if @frequency_type is NULL set @frequency_type = 4 -- Daily -- if @frequency_interval is NULL set @frequency_interval = 1 if @frequency_relative_interval is NULL set @frequency_relative_interval = 1 if @frequency_recurrence_factor is NULL set @frequency_recurrence_factor = 0 if @frequency_subday is NULL set @frequency_subday = 8 -- Hour -- if @frequency_subday_interval is NULL set @frequency_subday_interval = 1 if @active_start_time_of_day is NULL set @active_start_time_of_day = 0 if @active_end_time_of_day is NULL set @active_end_time_of_day = 235959 if @active_start_date is NULL set @active_start_date = 0 if @active_end_date is NULL set @active_end_date = 99991231 -- Add Perfmoon instance dbcc addinstance ("SQL Replication Merge", @name) IF @local_job = 1 BEGIN if @job_existing = 0 begin -- Construct task command -- select @command = '-Publisher ' + QUOTENAME(@publisher) + ' -PublisherDB ' + QUOTENAME(@publisher_db) + ' ' select @command = @command + '-Publication ' + QUOTENAME(@publication) + ' ' select @command = @command + '-Subscriber ' + QUOTENAME(@subscriber) + ' ' if (@subscriber_datasource_type = 0) select @command = @command + '-SubscriberDB ' + QUOTENAME(@subscriber_db) + ' ' if (@subscriber_datasource_type <> 0) select @command = @command + '-SubscriberType ' + convert(nvarchar(10),@subscriber_datasource_type) + ' ' select @command = @command + @optional_command_line select @command = @command + '-Distributor ' + QUOTENAME(@distributor) + ' ' -- Always use integrated for local connection select @command = @command + '-DistributorSecurityMode 1 ' if @hostname is not null and rtrim(ltrim(@hostname)) <> '' select @command = @command + '-HostName ' + quotename(@hostname) + ' ' select @database = db_name() -- Get Merge category name (assumes category_id = 14) select @category_name = name FROM msdb.dbo.syscategories where category_id = 14 EXEC @retcode = dbo.sp_MSadd_repl_job @name = @name, @subsystem = 'Merge', @server = @@SERVERNAME, @databasename = @database, @enabled = 1, @freqtype = @frequency_type, @freqinterval = @frequency_interval, @freqsubtype = @frequency_subday, @freqsubinterval = @frequency_subday_interval, @freqrelativeinterval = @frequency_relative_interval, @freqrecurrencefactor = @frequency_recurrence_factor, @activestartdate = @active_start_date, @activeenddate = @active_end_date, @activestarttimeofday = @active_start_time_of_day, @activeendtimeofday = @active_end_time_of_day, @command = @command, @category_name = @category_name, @failure_detection = 1, @agent_id = @agent_id, @retryattempts = 10, @retrydelay = 1, @job_login = @job_login, @job_password = @job_password, @job_id = @merge_jobid OUTPUT, @job_step_uid = @merge_job_step_uid OUTPUT if @@ERROR <> 0 or @retcode <> 0 goto UNDO -- note that we only raise the warning when called from -- 'PRE-YUKON' code. when called from 'YUKON ADD AGENT' the -- warning is not needed since it that case it was not implicit IF @internal = N'PRE-YUKON' BEGIN -- Warning: The merge agent job has been implicitly created and will run under the SQL Server Agent Service Account. RAISERROR(21452, 10, -1, 'merge') END end else begin select @merge_jobid = sjv.job_id, @merge_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.name = @name and sjv.master_server = 0 and UPPER(sjv.originating_server) = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName'))) and sjv.category_id = 14 and sjs.subsystem = N'Merge' and sjs.database_name = db_name() if @merge_jobid IS NULL begin -- Message from msdb.dbo.sp_verify_job_identifiers RAISERROR(14262, -1, -1, 'Merge Job', @name) GOTO UNDO end end END ELSE BEGIN -- Generate a job GUID for remote agents. This will be used by the UI to uniquely -- identify rows returned by the enums set @merge_jobid = newid(); set @merge_job_step_uid = NULL END END ELSE BEGIN -- Generate a job GUID even when we haven't created the job agent. This -- will be used by the UI to uniquely identify rows returned by the enums SELECT @merge_jobid = newid(), @merge_job_step_uid = NULL END UPDATE dbo.MSmerge_agents SET name = ISNULL(@name, N''), job_id = @merge_jobid, job_step_uid = @merge_job_step_uid WHERE id = @agent_id IF @@ERROR <> 0 GOTO UNDO COMMIT TRANSACTION tr_add_merge_agent RETURN(0) UNDO: if @@trancount > 0 begin ROLLBACK TRANSACTION tr_add_merge_agent COMMIT TRANSACTION end RETURN(1) end
No comments:
Post a Comment