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_distribution_agent(nvarchar @name, smallint @publisher_id
, nvarchar @publisher_db
, nvarchar @publication
, smallint @subscriber_id
, nvarchar @subscriber_db
, int @subscription_type
, 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
, int @retryattempts
, int @retrydelay
, nvarchar @command
, int @update_mode
, bit @offloadagent
, nvarchar @offloadserver
, nvarchar @dts_package_name
, nvarchar @dts_package_password
, int @dts_package_location
, smallint @subscriber_security_mode
, nvarchar @subscriber_login
, nvarchar @subscriber_password
, nvarchar @job_login
, nvarchar @job_password
, nvarchar @internal
, nvarchar @subscriber_provider
, nvarchar @subscriber_datasrc
, nvarchar @subscriber_location
, nvarchar @subscriber_provider_string
, nvarchar @subscriber_catalog)
MetaData:
CREATE PROCEDURE sys.sp_MSadd_distribution_agent ( @name sysname = NULL, @publisher_id smallint, @publisher_db sysname, @publication sysname, @subscriber_id smallint, @subscriber_db sysname, @subscription_type int, -- have to have it to identify a distribution agent. @local_job bit, @frequency_type int = 64, @frequency_interval int = 1, @frequency_relative_interval int = 1, @frequency_recurrence_factor int = 0, @frequency_subday int = 4, @frequency_subday_interval int = 5, @active_start_time_of_day int = 0, @active_end_time_of_day int = 235959, @active_start_date int = 0, @active_end_date int = 99991231, @retryattempts int = 10, @retrydelay int = 1, @command nvarchar(4000) = NULL, @agent_id int = NULL OUTPUT, @distribution_jobid binary(16) = NULL OUTPUT, @update_mode int = 0, -- Agent offload @offloadagent bit = 0, @offloadserver sysname = NULL, @dts_package_name sysname = NULL, @dts_package_password nvarchar(524) = NULL, @dts_package_location int = 0, -- used for subscription based security @subscriber_security_mode smallint = NULL, @subscriber_login sysname = NULL, @subscriber_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' -- used for heterogeneous subscriptions @subscriber_provider sysname = NULL, @subscriber_datasrc nvarchar(4000) = NULL, @subscriber_location nvarchar(4000) = NULL, @subscriber_provider_string nvarchar(4000) = NULL, @subscriber_catalog sysname = NULL ) AS BEGIN SET NOCOUNT ON -- -- Declarations. -- DECLARE @retcode int, @database sysname, @profile_id int, @distribution_type int, @publisher sysname, @publisher_type sysname, @loc_publisher_db sysname, @category_name sysname, @subscriber sysname, @publisher_database_id int, @queue_server sysname, @queue_id sysname, @distrib_job_step_uid uniqueidentifier, @subscriber_type int, @dsn_subscriber int, @oledb_subscriber int, @dsn_dbname sysname, @optional_cmdline nvarchar(4000), @independent_agent int, @job_existing bit, @comments nvarchar(255) SELECT @dsn_subscriber = 1, @oledb_subscriber = 3, @dsn_dbname = formatmessage(20586) -- 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_subscription can only be executed in the distribution database." RAISERROR(21482, 16, -1, 'sp_MSadd_subscription', '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 -- -- Initializations -- select @database = DB_NAME() -- Adjust the optional_commandline SELECT @optional_cmdline = ISNULL(RTRIM(LTRIM(@command)), N'') SELECT @command = NULL -- 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 or 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 TRAN tran_sp_MSadd_distribution_agent SAVE TRAN tran_sp_MSadd_distribution_agent -- Code for distribution agent type in MSagent_profiles -- SELECT @distribution_type = 3 SELECT @profile_id = profile_id FROM msdb..MSagent_profiles WHERE agent_type = @distribution_type AND def_profile = 1 IF @profile_id IS NULL GOTO UNDO SELECT @publisher = srvname FROM master.dbo.sysservers WHERE srvid = @publisher_id SELECT @subscriber = srvname FROM master.dbo.sysservers WHERE srvid = @subscriber_id SELECT @publisher_database_id = id FROM MSpublisher_databases WHERE publisher_id = @publisher_id AND publisher_db = @publisher_db -- Get publisher type EXEC @retcode = sys.sp_MSrepl_getdistributorinfo @publisher = @publisher, @publisher_type = @publisher_type OUTPUT IF @retcode != 0 OR @@ERROR != 0 BEGIN GOTO UNDO END -- 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 -- Encrypt the dts password before storing, but only do so if this -- procedure is called from sp_addpushsubscription_agent IF @internal <> N'PRE-YUKON' BEGIN EXEC @retcode = sys.sp_MSreplencrypt @dts_package_password OUTPUT IF @@ERROR <> 0 OR @retcode <> 0 GOTO UNDO END IF @internal = N'PRE-YUKON' OR @internal = N'YUKON ADD AGENT' BEGIN -- if this is a pull or anon case and a value is not provided for the -- security mode then we will default the value to be integrated. this -- makes sense because the subsec mode for pull is only used in the queued -- case and in that case we always want the sub to default to integrated IF @subscription_type != 0 AND @subscriber_security_mode is NULL BEGIN SELECT @subscriber_security_mode = 1 END -- 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 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 WINDOWS authentication then clear out the login/password IF @subscriber_security_mode = 1 BEGIN select @subscriber_login = '', @subscriber_password = newid() END -- Encrypt the password before storing EXEC @retcode = sys.sp_MSreplencrypt @subscriber_password OUTPUT IF @@ERROR <> 0 OR @retcode <> 0 GOTO UNDO END -- Get default task parameter values from MSsubscriber_info SELECT @subscriber_type = type FROM MSsubscriber_info WHERE UPPER(publisher) = UPPER(@publisher) AND UPPER(subscriber) = UPPER(@subscriber) -- Only SQL Server and OLEDB subscriber support dts IF @dts_package_name IS NOT NULL AND @subscriber_type NOT IN (0, 3) BEGIN -- Only sqlserver or oledb sub are allowed RAISERROR(21170, 16, -1) GOTO UNDO END END IF @internal = N'PRE-YUKON' OR @internal = N'YUKON ADD SUB' BEGIN IF @internal = N'YUKON ADD SUB' BEGIN -- When calling it from internal = 'YUKON ADD SUB' we know -- that the security information should not have been provided 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 -- Try to drop it first EXEC @retcode = sys.sp_MSdrop_distribution_agent @publisher_id = @publisher_id, @publisher_db = @publisher_db, @publication = @publication, @subscriber_id = @subscriber_id, @subscriber_db = @subscriber_db, @subscription_type = @subscription_type IF @@ERROR <> 0 or @retcode <> 0 GOTO UNDO -- -- If this is a queued subscription -- Process the queue creation -- if (@update_mode in (2,3,7)) begin -- -- MSMQ Queue being used -- exec @retcode = sys.sp_MSaddmqforsubscription @queue_server output, @queue_id output if (@retcode != 0 or @@error != 0) goto UNDO end else if (@update_mode in (4,5,6)) begin -- -- SQL Queue being used -- select @queue_id = N'mssqlqueue' end -- -- Insert row -- INSERT INTO MSdistribution_agents (name, publisher_database_id, publisher_id, publisher_db, publication, subscriber_id, subscriber_db, subscription_type, local_job, subscription_guid, profile_id, queue_id, queue_server, dts_package_name, dts_package_password, dts_package_location, subscriber_security_mode, subscriber_login, subscriber_password) VALUES ('',@publisher_database_id, @publisher_id, @publisher_db, @publication, @subscriber_id, @subscriber_db, @subscription_type, @local_job, newid(), @profile_id, @queue_id, @queue_server, @dts_package_name, @dts_package_password, @dts_package_location, @subscriber_security_mode, @subscriber_login, @subscriber_password) IF @@ERROR <> 0 GOTO UNDO SELECT @agent_id = @@IDENTITY -- -- For independant agent - Add an entry to history to indicate the subscription is uninitialized (runstatus = 0) -- if @publication is not null and (lower(@publication)<>'all') begin select @comments = isnull(formatmessage(21019), N'Message 21019') exec @retcode = sys.sp_MSadd_distribution_history @agent_id = @agent_id ,@runstatus = 0 ,@comments = @comments ,@updateable_row = 0 ,@do_raiserror = 0 IF @@ERROR <> 0 OR @retcode <> 0 GOTO UNDO end END ELSE IF @internal = N'YUKON ADD AGENT' BEGIN SELECT @agent_id = id FROM MSdistribution_agents WHERE publisher_id = @publisher_id AND publisher_db = @publisher_db AND publication = @publication AND subscriber_id = @subscriber_id AND subscriber_db = @subscriber_db AND subscription_type = @subscription_type IF @agent_id IS NULL BEGIN -- "The subscription could not be found." RAISERROR (20021, 16, -1) GOTO UNDO END UPDATE MSdistribution_agents SET dts_package_name = @dts_package_name, dts_package_password = @dts_package_password, dts_package_location = @dts_package_location, subscriber_security_mode = @subscriber_security_mode, subscriber_login = @subscriber_login, subscriber_password = @subscriber_password, subscriber_provider = @subscriber_provider, subscriber_datasrc = @subscriber_datasrc, subscriber_location = @subscriber_location, subscriber_provider_string = @subscriber_provider_string, subscriber_catalog = @subscriber_catalog WHERE id = @agent_id END -- Set agent name SELECT @subscriber = ISNULL(@subscriber, ''), @subscriber_db = ISNULL(@subscriber_db, '') IF @name IS NULL OR @name = N'' BEGIN -- -- Sacrifice 1-2 character from each of (@publisher,@publication, -- @publisher_db,subscriber) to allow 4 more indentity digits in -- the distribution agent name. This will hopefully provide better -- guarantee of agent name uniqueness. -- IF @publication is NOT NULL and (LOWER(@publication)<>'all') BEGIN SELECT @name = LEFT(@publisher, 21) + '-' + LEFT(@loc_publisher_db, 21) + '-' + LEFT(@publication, 21) + '-' + LEFT(@subscriber, 21) + '-' + CONVERT(nvarchar(21), @agent_id) END ELSE BEGIN SELECT @name = LEFT(@publisher, 28) + '-' + LEFT(@loc_publisher_db, 28) + '-' + LEFT(@subscriber, 28) + '-' + CONVERT(nvarchar(28), @agent_id) END -- If creating a new job and the generated name already -- exists, re-generate the name with a guid appended 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 IF @publication is NOT NULL and (LOWER(@publication)<>'all') BEGIN SELECT @name = fn_repluniquename(newid(), @publisher, @loc_publisher_db, @publication, @subscriber) END ELSE BEGIN SELECT @name = fn_repluniquename(newid(), @publisher, @loc_publisher_db, @subscriber, null) END END SELECT @job_existing = 0 END ELSE BEGIN SELECT @job_existing = 1 END IF @internal = N'PRE-YUKON' OR @internal = N'YUKON ADD AGENT' BEGIN -- Reset @publication if shared agent SELECT @independent_agent = independent_agent FROM dbo.MSpublications WHERE publisher_id = @publisher_id AND publisher_db = @publisher_db AND publication = @publication IF @independent_agent = 0 SELECT @publication = 'ALL' -- Check the existance of the package if it is at the distributor side. if @dts_package_name is not null and @dts_package_location = 0 begin exec @retcode = dbo.sp_MSrepl_validate_dts_package @name = @dts_package_name IF @@ERROR <> 0 or @retcode <> 0 GOTO UNDO end -- Add Perfmon instance dbcc addinstance ("SQL Replication Distribution", @name) IF @local_job = 1 BEGIN if @job_existing = 0 begin -- Construct task command SELECT @command = '-Subscriber ' + QUOTENAME(@subscriber) + ' ' -- DSN subscribers don't have a subscriber db name. -- 7.0 publisher still uses DSN. 8.0 publisher use localized '(default destination)' -- ActiveX may use unlocalized '(default destination)' IF @subscriber_db IS NOT NULL AND @subscriber_db NOT IN( N'(default destination)', N'DSN', @dsn_dbname) BEGIN SELECT @command = @command + '-SubscriberDB ' + QUOTENAME(@subscriber_db) + ' ' END SELECT @command = @command + '-Publisher ' + QUOTENAME(@publisher) + ' ' SELECT @command = @command + '-Distributor ' + QUOTENAME(@@SERVERNAME) + ' ' -- Always use integrated security for the local connection SELECT @command = @command + '-DistributorSecurityMode 1 ' IF @independent_agent = 1 SELECT @command = @command + '-Publication ' + QUOTENAME(@publication) + ' ' IF @publisher_db IS NOT NULL SELECT @command = @command + '-PublisherDB ' + QUOTENAME(@publisher_db) + ' ' IF @subscriber_type = @dsn_subscriber OR @subscriber_type = @oledb_subscriber BEGIN SELECT @command = @command + '-SubscriberType ' + convert (nvarchar(10), @subscriber_type) + ' ' END IF @dts_package_name IS NOT NULL SELECT @command = @command + '-UseDTS ' IF datalength(@command) + datalength(@optional_cmdline) > 8000 BEGIN -- "The @optional_command_line is too long. Use an agent definition file." RAISERROR(20018, 16, -1) GOTO UNDO END SELECT @command = @command + N' ' + @optional_cmdline + N' ' DECLARE @nullchar nchar(20) SELECT @nullchar = NULL -- Get Distribution category name (assumes category_id = 10) select @category_name = name FROM msdb.dbo.syscategories where category_id = 10 if @frequency_recurrence_factor is null select @frequency_recurrence_factor = 0 EXECUTE @retcode = dbo.sp_MSadd_repl_job @name = @name, @subsystem = 'Distribution', @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, @nextrundate = 0, @nextruntime = 0, @runpriority = 0, @emailoperatorname = NULL, @retryattempts = @retryattempts, @retrydelay = @retrydelay, @command = @command, @loghistcompletionlevel = 0, @emailcompletionlevel = 0, @description = NULL, @category_name = @category_name, @failure_detection = 1, @agent_id = @agent_id, @job_login = @job_login, @job_password = @job_password, @job_id = @distribution_jobid OUTPUT, @job_step_uid = @distrib_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 distribution agent job has been implicitly created and will run under the SQL Server Agent Service Account. RAISERROR(21452, 10, -1, 'distribution') END end else begin -- retrieve the agent job step uid select @distribution_jobid = sjv.job_id, @distrib_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 = 10 and sjs.subsystem = N'Distribution' and sjs.database_name = db_name() if @distribution_jobid IS NULL begin -- Message from msdb.dbo.sp_verify_job_identifiers RAISERROR(14262, -1, -1, 'Distribution 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 @distribution_jobid = newid(); set @distrib_job_step_uid = NULL END END ELSE IF @internal = N'YUKON ADD SUB' 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 @distribution_jobid = newid(), @distrib_job_step_uid = NULL END UPDATE MSdistribution_agents SET name = ISNULL(@name, ''), job_id = @distribution_jobid, job_step_uid = @distrib_job_step_uid WHERE id = @agent_id IF @@ERROR <> 0 GOTO UNDO -- -- commit the transaction -- COMMIT TRAN -- -- all done -- RETURN(0) UNDO: IF @internal = N'PRE-YUKON' OR @internal = N'YUKON ADD SUB' BEGIN -- -- delete the MSMQ queue if necessary -- if (@update_mode in (2,3,7) and @queue_server IS NOT NULL and @queue_id IS NOT NULL) begin exec sys.sp_MSdropmqforsubscription @queue_server, @queue_id end END if @@TRANCOUNT > 0 begin ROLLBACK TRAN tran_sp_MSadd_distribution_agent COMMIT TRAN end return(1) END
No comments:
Post a Comment