Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSchange_repl_job_unsafe

  No additional text.


Syntax
CREATE PROCEDURE sys.sp_MSchange_repl_job_unsafe
(
	@id 							uniqueidentifier,
	@step_uid						uniqueidentifier,
	@name							nvarchar(100) = NULL,
	@frequency_type 				int = NULL,
	@frequency_interval 			int = NULL,
	@frequency_subday				int = NULL,
	@frequency_subday_interval		int = NULL,
	@frequency_relative_interval	int = NULL,
	@frequency_recurrence_factor	int = NULL,
	@active_start_date				int = NULL,
	@active_end_date				int = NULL,
	@active_start_time_of_day		int = NULL,
	@active_end_time_of_day 		int = NULL,
	@login							nvarchar(257) = NULL,
	@password						sysname = NULL,
	@proxy_id                       int = NULL OUTPUT
)
WITH EXECUTE AS 'dbo'
AS
BEGIN
	DECLARE @retcode			int,
			@command			nvarchar(4000),
			@schedule_name		sysname,
			@schedule_id		int,
			@credential 		sysname,
			@identity			sysname,
			@step_id			int,
			@subsystem			sysname,
			@owner_login_name	sysname,
			@temp_sched_name	sysname
			
	-- set schedule name
	SELECT @command 			= NULL,
			@schedule_name		= ISNULL(formatmessage(20532), N'Message 20532'),
			@schedule_id		= 0,
			@credential 		= NULL,
			@identity			= NULL,
			@step_id			= -1,
			@subsystem 			= NULL,
			@owner_login_name	= NULL

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

	BEGIN TRANSACTION tr_sp_MSchangerepljob
	SAVE TRANSACTION tr_sp_MSchangerepljob

	-- modify the job name if it was provided
	IF @name IS NOT NULL
	BEGIN		
		EXEC @retcode = msdb.dbo.sp_update_job @job_id	= @id,
											@new_name	= @name
		IF @@error <> 0 OR @retcode <> 0
			GOTO FAILURE
	END
	
	-- modify the job schedule if it was provided
	IF @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
		-- if we are setting @frequency_type to on-demand then we
		-- need to enumerate through the job schedules and drop them
		IF @frequency_type = 0x2
		BEGIN
		  	DECLARE #repl_jobschedules CURSOR LOCAL FAST_FORWARD
				FOR SELECT sjs.schedule_id,
							sss.name
						FROM msdb.dbo.sysjobschedules sjs
							JOIN msdb.dbo.sysschedules sss
								ON sjs.schedule_id = sss.schedule_id
						WHERE job_id = @id

			OPEN #repl_jobschedules

			FETCH #repl_jobschedules INTO @schedule_id, @temp_sched_name
			
			WHILE @@FETCH_STATUS <> -1
			BEGIN
				-- first detach the schedule from the current job
				EXEC @retcode = msdb.dbo.sp_detach_schedule @job_id = @id,
																@schedule_id = @schedule_id
				IF @@ERROR <> 0 OR @retcode <> 0
					GOTO FAILURE

				-- next... if the schedule is created by repl and there
				-- are no other jobs using it we will delete the schedule
				IF @temp_sched_name = @schedule_name
					AND NOT EXISTS(SELECT *
									FROM msdb.dbo.sysjobschedules
									WHERE schedule_id = @schedule_id)
				BEGIN
					EXEC @retcode = msdb.dbo.sp_delete_schedule @schedule_id = @schedule_id
					IF @@ERROR <> 0 OR @retcode <> 0
						GOTO FAILURE
				END
				
				FETCH #repl_jobschedules INTO @schedule_id, @temp_sched_name
			END

			CLOSE #repl_jobschedules
			DEALLOCATE #repl_jobschedules
		END
		-- else if the job schedule already exists then update it
		ELSE IF EXISTS(SELECT sjs.schedule_id,
							sss.name
						FROM msdb.dbo.sysjobschedules sjs
							JOIN msdb.dbo.sysschedules sss
								ON sjs.schedule_id = sss.schedule_id
						WHERE job_id = @id)
		BEGIN
			EXEC @retcode = msdb.dbo.sp_update_jobschedule @job_id					= @id,
															@name					= @schedule_name,
															@freq_type				= @frequency_type,
															@freq_interval			= @frequency_interval,
															@freq_subday_type		= @frequency_subday,
															@freq_subday_interval	= @frequency_subday_interval,
															@freq_relative_interval = @frequency_relative_interval,
															@freq_recurrence_factor = @frequency_recurrence_factor,
															@active_start_date		= @active_start_date,
															@active_end_date		= @active_end_date,
															@active_start_time		= @active_start_time_of_day,
															@active_end_time		= @active_end_time_of_day
			IF @@error <> 0 OR @retcode <> 0
				GOTO FAILURE	
		END
		-- else we will add the job schedule
		ELSE
		BEGIN
			EXEC @retcode = msdb.dbo.sp_add_jobschedule @job_id                 = @id,
												        @name                   = @schedule_name,
												        @enabled                = 1,
												        @freq_type              = @frequency_type,
												        @freq_interval          = @frequency_interval,
												        @freq_subday_type       = @frequency_subday,
												        @freq_subday_interval   = @frequency_subday_interval,
												        @freq_relative_interval = @frequency_relative_interval,
												        @freq_recurrence_factor = @frequency_recurrence_factor,
												        @active_start_date      = @active_start_date,
												        @active_end_date        = @active_end_date,
												        @active_start_time      = @active_start_time_of_day,
												        @active_end_time        = @active_end_time_of_day
								
    		IF @@error <> 0 OR @retcode <> 0
				GOTO FAILURE		
		END
	END

    IF @proxy_id IS NOT NULL
    BEGIN

		SELECT @step_id = step_id
			FROM msdb.dbo.sysjobsteps
			WHERE step_uid = @step_uid

        EXEC @retcode = msdb..sp_update_jobstep @job_id = @id,
	        										@step_id = @step_id,
	        										@proxy_id = @proxy_id
		IF @@ERROR <> 0 or @retcode <> 0
            goto FAILURE

    END

	-- modify the jobstep proxy account if it was provided
	IF @login IS NOT NULL
		OR @password IS NOT NULL
	BEGIN
		-- retrieve the credential and identity
		SELECT @credential = sc.name,
				@identity = sc.credential_identity
			FROM msdb.dbo.sysjobs_view as sjv
				JOIN msdb.dbo.sysjobsteps as sjs
					ON sjv.job_id = sjs.job_id
				JOIN msdb.dbo.sysproxies sp
					ON sjs.proxy_id = sp.proxy_id
				JOIN sys.credentials sc
					ON sp.credential_id = sc.credential_id
			WHERE sjv.master_server = 0
				AND UPPER(sjv.originating_server) = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName')))
				AND sjs.step_uid = @step_uid

		-- if @credential is not null then that means the proxy account already
		-- exists and we should be able to just update the value of the account
		IF @credential IS NOT NULL
		BEGIN
			-- change of login name
			IF @login IS NOT NULL
			BEGIN
				SELECT @password = ISNULL(@password, N'')
				
				SELECT @command = 'ALTER CREDENTIAL ' + QUOTENAME(@credential) +
									' WITH IDENTITY = ' + QUOTENAME(@login, '''') +
									', SECRET = ' + QUOTENAME(@password, '''')
					
				EXEC(@command)
				IF @@error <> 0
					GOTO FAILURE
			END
			-- change of password only
			ELSE IF @password IS NOT NULL
			BEGIN
				SELECT @command = 'ALTER CREDENTIAL ' + QUOTENAME(@credential) +
										' WITH IDENTITY = ' + QUOTENAME(@identity, '''') +
										', SECRET = ' + QUOTENAME(@password, '''')
										
				EXEC(@command)
				IF @@error <> 0
					GOTO FAILURE
			END
		END
		-- this means we are coming from an upgrade scenario and the proxy account has never
		-- been added before. we will add the proxy account and associate it to the job
		ELSE
		BEGIN
			SELECT @step_id = step_id,
					@subsystem = subsystem,
					@proxy_id = 0
				FROM msdb.dbo.sysjobsteps
				WHERE step_uid = @step_uid

			SELECT @name = ISNULL(@name, name),
					@owner_login_name = SUSER_SNAME(owner_sid)
				FROM msdb.dbo.sysjobs
				WHERE job_id = @id
				
			EXEC @retcode = sys.sp_MSrepladdproxyaccount @job_name = @name,
														@owner_login_name = @owner_login_name,
														@subsystem = @subsystem,
														@job_login = @login,
														@job_password = @password,
														@proxy_id = @proxy_id OUTPUT
			IF @@ERROR <> 0 or @retcode <> 0
	            goto FAILURE
				
	        EXEC @retcode = msdb..sp_update_jobstep @job_id = @id,
	        										@step_id = @step_id,
	        										@proxy_id = @proxy_id
			IF @@ERROR <> 0 or @retcode <> 0
	            goto FAILURE
		END
	END
	
	COMMIT TRANSACTION

	RETURN 0
FAILURE:
	ROLLBACK TRANSACTION tr_sp_MSchangerepljob
	COMMIT TRANSACTION

	RETURN 1
END

 
Last revision 2008RTM
See also

  sp_MSchange_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