Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSadd_repl_job

  No additional text.


Syntax
create procedure sys.sp_MSadd_repl_job
  @name                   nvarchar(200),
  @subsystem              nvarchar(60) = 'TSQL',
  @owner_login_name		  nvarchar(256)= NULL,
  @server                 sysname      = NULL,
  @username               sysname      = NULL,
  @databasename           sysname      = NULL,
  @enabled                TINYINT      = 0,
  @freqtype               INT          = 2, -- 2 means OnDemand
  @freqinterval           INT          = 1,
  @freqsubtype            INT          = 1,
  @freqsubinterval        INT          = 1,
  @freqrelativeinterval   INT          = 1,
  @freqrecurrencefactor   INT          = 1,
  @activestartdate        INT          = 0,
  @activeenddate          INT          = 0,
  @activestarttimeofday   INT          = 0,
  @activeendtimeofday     INT          = 0,
  @nextrundate            INT          = 0,
  @nextruntime            INT          = 0,
  @runpriority            INT          = 0,
  @emailoperatorname      nvarchar(100)= NULL,
  @retryattempts          INT          = NULL,
  @retrydelay             INT          = 0,
  @command                nvarchar(4000)= NULL,
  @loghistcompletionlevel INT          = 2,
  @emailcompletionlevel   INT          = 0,
  @description            nvarchar(255)= NULL,
  @tagadditionalinfo      nvarchar(96) = NULL,
  @tagobjectid            INT          = NULL,
  @tagobjecttype          INT          = NULL,
  @cmdexecsuccesscode     INT          = 0,
  @category_name          sysname      = NULL, -- New for 7.0
  @failure_detection      BIT          = 0,
  @agent_id               INT          = NULL,
  @proxy_id               INT          = NULL,    -- Either provide @proxy_id or @job_login & @job_password
  @job_login              nvarchar(257)= NULL,
  @job_password           sysname      = NULL,
  @job_id                 BINARY(16)   = NULL OUTPUT,
  @job_step_uid           UNIQUEIDENTIFIER = NULL OUTPUT
AS
BEGIN
	DECLARE @retcode 	int,
			@count 		int,
			@count_str	nvarchar(4000),
			@len		int
	
	-- Verify that the job to be added is a repl job
	IF @subsystem NOT IN (N'TSQL',
							N'Snapshot',
							N'LogReader',
							N'Distribution',
							N'Merge',
							N'QueueReader')
		OR @category_name NOT IN (N'REPL-Alert Response',
									N'REPL-Checkup',
									N'REPL-Distribution',
									N'REPL-Distribution Cleanup',
									N'REPL-History Cleanup',
									N'Replication',
									N'REPL-LogReader',
									N'REPL-Merge',
									N'REPL-QueueReader',
									N'REPL-Snapshot',
									N'REPL-Subscription Cleanup')

	BEGIN
		-- Only replication jobs or job schedules can be added, modified or dropped via replication stored procedures.
		RAISERROR(22538, 16, -1)
		RETURN 1
	END

	-- if the following settings are null we will set the values
	SELECT @owner_login_name 	= ISNULL(@owner_login_name, SUSER_SNAME()),
			@databasename		= ISNULL(@databasename, DB_NAME()),
			@count 				= 0

	-- If a job with this name already exist we will attempt
	-- to generate a new unique name and then continue ...
    WHILE EXISTS (SELECT *
    			FROM msdb.dbo.sysjobs_view
    			WHERE name = @name collate database_default)
    BEGIN
       	SELECT @count = @count + 1
       	
    	SELECT @count_str = N'.' + CONVERT(nvarchar(4000), @count)

	   	IF LEN(@name) + LEN(@count_str) < 200
	   	BEGIN
			SELECT @len = LEN(@name)
	   	END
	   	ELSE
	   	BEGIN
			SELECT @len = LEN(@name) - LEN(@count_str)
	   	END
	   	
		SELECT @name = LEFT(@name, @len) + @count_str
    END

	-- call internal add procedure
    EXEC @retcode = sys.sp_MSadd_repl_job_unsafe @name = @name,
											    @subsystem = @subsystem,
											    @owner_login_name = @owner_login_name,
											    @server = @server,
											    @username = @username,
											    @databasename = @databasename,
											    @enabled = @enabled,
											    @freqtype = @freqtype,
											    @freqinterval = @freqinterval,
											    @freqsubtype = @freqsubtype,
											    @freqsubinterval = @freqsubinterval,
											    @freqrelativeinterval = @freqrelativeinterval,
											    @freqrecurrencefactor = @freqrecurrencefactor,
											    @activestartdate = @activestartdate,
											    @activeenddate = @activeenddate,
											    @activestarttimeofday = @activestarttimeofday,
											    @activeendtimeofday = @activeendtimeofday,
											    @nextrundate = @nextrundate,
											    @nextruntime = @nextruntime,
											    @runpriority = @runpriority,
											    @emailoperatorname = @emailoperatorname,
											    @retryattempts = @retryattempts,
											    @retrydelay = @retrydelay,
											    @command = @command,
											    @loghistcompletionlevel = @loghistcompletionlevel,
											    @emailcompletionlevel = @emailcompletionlevel,
											    @description = @description,
											    @tagadditionalinfo = @tagadditionalinfo,
											    @tagobjectid = @tagobjectid,
											    @tagobjecttype = @tagobjecttype,
											    @cmdexecsuccesscode = @cmdexecsuccesscode,
											    @category_name = @category_name,
											    @failure_detection = @failure_detection,
											    @agent_id = @agent_id,
											    @proxy_id = @proxy_id,
											    @job_login = @job_login,
											    @job_password = @job_password,
											    @job_id = @job_id OUTPUT,
											    @job_step_uid = @job_step_uid OUTPUT

    RETURN @retcode
END

 
Last revision 2008RTM
See also

  sp_adddistributiondb (Procedure)
sp_addmergepullsubscription_agent (Procedure)
sp_addpullsubscription_agent (Procedure)
sp_MSadddynamicsnapshotjobatdistributor (Procedure)
sp_MSadd_distribution_agent (Procedure)
sp_MSadd_distributor_alerts_and_responses (Procedure)
sp_MSadd_logreader_agent (Procedure)
sp_MSadd_merge_agent (Procedure)
sp_MSadd_qreader_agent (Procedure)
sp_MSadd_repl_job_unsafe (Procedure)
sp_MSadd_snapshot_agent (Procedure)
sp_MScreate_replication_checkup_agent (Procedure)
sp_MSrepl_add_expired_sub_cleanup_job (Procedure)
sp_vupgrade_replsecurity_metadata (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash