Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSadd_repl_job_unsafe

  No additional text.


Syntax
create procedure sys.sp_MSadd_repl_job_unsafe
(
  @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
)
WITH EXECUTE AS 'dbo'
AS
BEGIN
    DECLARE @retval INT
    declare @step_id int
    declare @step_name nvarchar(100)
    declare @step_command nvarchar(1024)
    declare @on_fail_action tinyint
    declare @on_success_action tinyint
    declare @schedule_name nvarchar(100)
    declare @comments nvarchar(100)
    declare @job_domain sysname
    declare @create_credential_ddl nvarchar(4000)
    declare @proxy_credential_name sysname
    declare @maxpartlen int
    declare @count int
    declare @count_str sysname
    declare @iscontinuous bit

    SET NOCOUNT ON

    SELECT @retval = 1 -- 0 means success, 1 means failure
    set @step_id = 1
    set @on_fail_action = 2    -- Return failure
    set @on_success_action = 1    -- Return success
    set @step_command = NULL
    set @iscontinuous = 0

    SELECT @databasename = ISNULL(@databasename, DB_NAME())

    -- Return immediately if this is running on SQLExpress or SQLWeb
    IF (sys.fn_MSrepl_editionid () in (22, 40))
        RETURN 0

    /*
    ** Set default retries to every minute for 10 minutes.
    **
    */
    if @retryattempts = NULL
    	and @retrydelay = 0
    begin
     	select @retryattempts = 10
     	select @retrydelay = 1
    end
	
    BEGIN TRANSACTION
    SAVE TRANSACTION sp_MSadd_repl_job

    -- ensure that only one is specified
    IF @proxy_id IS NOT NULL
        AND @job_login IS NOT NULL
    BEGIN
        -- MSG : not localized because it is only for DEVs using this proc
        RAISERROR('@proxy_id and @job_login are mutually exclusive. Specify one or the other.', 16, -1)
        goto UNDO
    END
    -- proxy id provided so we only need to grant login to proxy
    ELSE IF @proxy_id IS NOT NULL
    BEGIN
    	-- get an applock to avoid failures from another
		-- repl user granting login to proxy for the same account
		-- exit if it takes longer than 30 seconds....
		EXEC @retval = sys.sp_getapplock @Resource = 'Create_Repl_Credential',
	                                    @LockMode = 'Exclusive',
	                                    @LockOwner = 'Transaction',
	                                    @LockTimeout = -1, -- infinite
	                                    @DbPrincipal = N'db_owner'
	    IF @@ERROR <> 0 or @retval < 0
			GOTO UNDO

    	CREATE TABLE #enm_login_for_proxy (proxy_id int, proxy_name sysname, flags int, name sysname, sid varbinary(85), principal_id int)
    	
		INSERT INTO #enm_login_for_proxy
			EXEC msdb.dbo.sp_enum_login_for_proxy @name = @owner_login_name,
													@proxy_id = @proxy_id
	
        SELECT @job_login = NULL,
                @job_password = NULL

		IF ISNULL(IS_SRVROLEMEMBER('sysadmin', @owner_login_name), 0) = 0
			AND NOT EXISTS(SELECT *
						FROM #enm_login_for_proxy
						WHERE proxy_id = @proxy_id
							AND sid = SUSER_SID(@owner_login_name, 0))
		BEGIN
			EXEC @retval = msdb.dbo.sp_grant_login_to_proxy @proxy_id = @proxy_id,
															@login_name = @owner_login_name
			IF @@ERROR <> 0 or @retval <> 0
				GOTO UNDO
		END
			
		DROP TABLE #enm_login_for_proxy

		-- we release the applock at this point because
		-- the remaining steps will not be affected by
		-- any type of name collisions etc...
	    EXEC @retval = sys.sp_releaseapplock @Resource = 'Create_Repl_Credential',
						                            @LockOwner =  'Transaction',
						                            @DbPrincipal =  'db_owner'
	    IF @@ERROR <> 0 or @retval <> 0
			GOTO UNDO
    END
    -- job login info should be provided
    -- Here we will create a CREDENTIAL for this job
    ELSE IF @job_login IS NOT NULL
    BEGIN
    	EXEC @retval = sys.sp_MSrepladdproxyaccount @job_name = @name,
    												@owner_login_name = @owner_login_name,
													@subsystem = @subsystem,
													@job_login = @job_login,
													@job_password = @job_password,
													@proxy_id = @proxy_id OUTPUT
		IF @@ERROR<>0 or @retval<>0
            goto UNDO
    END

    -- Add the job
    EXECUTE @retval = msdb.dbo.sp_add_job
      @job_name                   = @name,
      @enabled                    = @enabled,
      @start_step_id              = 1,
      @description                = @description,
      @category_name              = @category_name,
      @notify_level_eventlog      = @loghistcompletionlevel,
      @notify_level_email         = @emailcompletionlevel,
      @notify_email_operator_name = @emailoperatorname,
      @owner_login_name			  = @owner_login_name,
      @job_id                     = @job_id OUTPUT

    IF (@retval <> 0)
    BEGIN
        GOTO UNDO
    END

    -- Add startup message step
    if @failure_detection = 1
    begin

        select @step_name =
            case UPPER(@subsystem collate SQL_Latin1_General_CP1_CS_AS)
                when N'SNAPSHOT' then isnull(formatmessage(21410), N'Message 21410')
                when N'LOGREADER' then isnull(formatmessage(20528), N'Message 20528')
                when N'DISTRIBUTION' then isnull(formatmessage(21411), N'Message 21411')
                when N'MERGE' then isnull(formatmessage(21412), N'Message 21412')
                when N'QUEUEREADER' then isnull(formatmessage(21422), N'Message 21422')
            end
            ,@comments = isnull(formatmessage(20529), N'Message 20529')

        -- Construct command based on subsystem type
        select @step_command =
            case UPPER(@subsystem collate SQL_Latin1_General_CP1_CS_AS)
                WHEN 'SNAPSHOT' THEN
                N'sp_MSadd_snapshot_history @perfmon_increment = 0,  @agent_id = ' +
                    convert (nvarchar(10), @agent_id) + N', @runstatus = 1,
                    @comments = N''' + @comments + ''''
                WHEN 'LOGREADER' THEN
                N'sp_MSadd_logreader_history @perfmon_increment = 0, @agent_id = ' +
                    convert (nvarchar(10), @agent_id) + N', @runstatus = 1,
                    @comments = N''' + @comments + ''''
                WHEN 'DISTRIBUTION' THEN
                N'sp_MSadd_distribution_history @perfmon_increment = 0, @agent_id = ' +
                    convert (nvarchar(10), @agent_id) + N', @runstatus = 1,
                    @comments = N''' + @comments + ''''
                WHEN 'MERGE' THEN
                N'sp_MSadd_merge_history @perfmon_increment = 0, @agent_id = ' +
                    convert (nvarchar(10),@agent_id) + N', @runstatus = 1,
                    @comments = N''' + @comments + ''''
                WHEN 'QUEUEREADER' THEN
                N'sp_MSadd_qreader_history @perfmon_increment = 0, @agent_id = ' +
                    convert (nvarchar(10), @agent_id) + N', @runstatus = 1,
                    @comments = N''' + @comments + ''''
            end

        -- Add the job step
        EXECUTE @retval = msdb.dbo.sp_add_jobstep_internal
          @job_id                = @job_id,
          @step_id               = @step_id,
          @step_name             = @step_name,
          @command               = @step_command,
          @cmdexec_success_code  = @cmdexecsuccesscode,
          @on_success_action     = 3,    -- Goto next step
          @on_fail_action        = 3,    -- Goto next step
          @server                = @server,
          @database_name         = @databasename,
          @database_user_name    = @username,
          @os_run_priority       = @runpriority

        IF (@retval <> 0)
        BEGIN
            GOTO UNDO
        END

        set @step_id = @step_id + 1
        set @on_fail_action = 3            -- Goto next step
    end

	-- if frequency type is 64 (autostart) then
	-- we key off of this and set continuous mode to 1.
	-- in continuous mode we want the agent to retry
	-- every minute as many times so we're fault tolerant
	IF @freqtype = 0x40
	BEGIN
		SELECT @iscontinuous = 1,
				@retryattempts = 2147483647,
				@retrydelay = 1
	END
	
    -- Add the job step
     select @step_name = isnull(formatmessage(20530), N'Message 20530')
    EXECUTE @retval = msdb.dbo.sp_add_jobstep_internal
      @job_id                = @job_id,
      @step_id               = @step_id,
      @step_name             = @step_name,
      @subsystem             = @subsystem,
      @command               = @command,
      @cmdexec_success_code  = @cmdexecsuccesscode,
      @on_success_action     = @on_success_action,
      @on_fail_action        = @on_fail_action,
      @server                = @server,
      @database_name         = @databasename,
      @database_user_name    = @username,
      @retry_attempts        = @retryattempts,
      @retry_interval        = @retrydelay,
      @os_run_priority       = @runpriority,
      @proxy_id              = @proxy_id,
      @step_uid              = @job_step_uid OUTPUT

    IF (@retval <> 0)
    BEGIN
        GOTO UNDO
    END

    -- Add failure message step
    if @failure_detection = 1
    begin
        -- Construct command
        select @step_id = @step_id + 1
                ,@step_name = isnull(formatmessage(20531), N'Message 20531')
                ,@step_command = N'sp_MSdetect_nonlogged_shutdown @subsystem = ''' + @subsystem + N''', @agent_id = ' + convert (nvarchar(10), @agent_id)

        -- Add the job step
        EXECUTE @retval = msdb.dbo.sp_add_jobstep_internal
          @job_id                = @job_id,
          @step_id               = @step_id,
          @step_name             = @step_name,
          @command               = @step_command,
          @cmdexec_success_code  = @cmdexecsuccesscode,
          @on_success_action     = 2,                    -- Always quit with failure
          @server                = @server,
          @database_name         = @databasename,
          @database_user_name    = @username,
          @os_run_priority       = @runpriority

        IF (@retval <> 0)
        BEGIN
            GOTO UNDO
        END
    end

    -- Add the job schedule
    IF (@activestartdate = 0)
      	SELECT @activestartdate = NULL
    IF (@activeenddate = 0)
    	SELECT @activeenddate = NULL

    -- But if @activeenddate is NOT NULL, then @activestartdate cannot be allowed to be NULL either.  Set it to today's date converted to the int format used yyyymmdd
    IF (@activeenddate IS NOT NULL AND @activestartdate IS NULL)
    	SELECT @activestartdate=DATEPART(YYYY,getdate()) * 10000 + DATEPART(MM,getdate()) * 100 + DATEPART(DD,getdate())

    -- But never let startdate be > end date
    IF (@activestartdate > @activeenddate)
    	SELECT @activestartdate=@activeenddate
	IF (@activestarttimeofday = 0)
		SELECT @activestarttimeofday = NULL
	IF (@activeendtimeofday = 0)
		SELECT @activeendtimeofday = NULL
	
    IF (@freqtype <> 0x2) -- OnDemand tasks simply have no schedule in 7.0
    BEGIN
		SELECT @schedule_name = isnull(formatmessage(20532), N'Message 20532')

		EXECUTE @retval = msdb.dbo.sp_add_jobschedule
					@job_id                 = @job_id,
					@name                   = @schedule_name,
					@enabled                = 1,
					@freq_type              = @freqtype,
					@freq_interval          = @freqinterval,
					@freq_subday_type       = @freqsubtype,
					@freq_subday_interval   = @freqsubinterval,
					@freq_relative_interval = @freqrelativeinterval,
					@freq_recurrence_factor = @freqrecurrencefactor,
					@active_start_date      = @activestartdate,
					@active_end_date        = @activeenddate,
					@active_start_time      = @activestarttimeofday,
					@active_end_time        = @activeendtimeofday

		IF (@retval <> 0)
		BEGIN
			GOTO UNDO
		END
    END

    -- And finally, add the job server
    EXECUTE @retval = msdb.dbo.sp_add_jobserver @job_id = @job_id, @server_name  = '(local)'
    IF (@retval <> 0)
    BEGIN
      GOTO UNDO
    END

  	COMMIT TRANSACTION

	-- If this is an Continuous LogReader or Distribution or Merge job, add the [new] '-Continuous' paramter to the command
	IF @iscontinuous = 1
		AND (UPPER(@subsystem collate SQL_Latin1_General_CP1_CS_AS) = 'LOGREADER'
			OR UPPER(@subsystem collate SQL_Latin1_General_CP1_CS_AS) = 'DISTRIBUTION'
			OR UPPER(@subsystem collate SQL_Latin1_General_CP1_CS_AS) = 'MERGE'
			OR UPPER(@subsystem collate SQL_Latin1_General_CP1_CS_AS) = 'QUEUEREADER')
	BEGIN
		UPDATE msdb.dbo.sysjobsteps
			SET command = command + ' -Continuous'
			WHERE (job_id = @job_id)
				AND ((@failure_detection = 0 and step_id = 1) or (@failure_detection = 1 and step_id = 2))
	END

	-- If this is an Continuous job, start it now (for backwards compatibility with 6.x SQLExecutive behaviour)
	IF @iscontinuous = 1
		AND @enabled = 1
	BEGIN
		EXECUTE msdb.dbo.sp_start_job @job_id = @job_id, @error_flag = 0
	END
	
  	RETURN(0)
UNDO:
    ROLLBACK TRANSACTION sp_MSadd_repl_job
    COMMIT TRANSACTION

    RETURN(1)
END

 
Last revision 2008RTM
See also

  sp_MSadd_repl_job (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