Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_cdc_add_job_internal

  No additional text.


Syntax
create procedure sys.sp_cdc_add_job_internal
(
    @job_type nvarchar(20),
    @start_job bit,
    @maxtrans int,
    @maxscans int,
    @continuous bit,
    @pollinginterval bigint,
    @retention bigint,
    @threshold bigint
)
with execute as 'dbo'
as
begin
    set nocount on

    declare @job_name sysname
           ,@retval   int
           ,@job_id   uniqueidentifier
           ,@old_job_id   uniqueidentifier
           ,@job_step_uid  uniqueidentifier
           ,@index    int
           ,@category_name sysname
           ,@command  nvarchar(1000)
           ,@server   sysname
           ,@databasename sysname
           ,@user     sysname
           ,@schedule_name sysname
           ,@database_id int
           ,@valid_job bit
           ,@description nvarchar(100)
           ,@step_name nvarchar(100)
		   ,@step_id int
		   ,@action nvarchar(1000)
		   ,@raise_error int
		   ,@raise_message nvarchar(4000)
		   ,@trancount int
		   ,@db_name sysname
		
	set @db_name = db_name()	

    -- Wrap transaction processing within a TRY/CATCH block
    BEGIN TRY
		
		set @trancount = @@trancount

		begin tran
		save tran tr_sp_cdc_add_job

		
		-- If the cdc_jobs table does not exist, create it
		
		if object_id(N'msdb.dbo.cdc_jobs') is null
		begin
			set @action = N'create table msdb.dbo.cdc_jobs'
			create table msdb.dbo.cdc_jobs
			(
				database_id int not null,
				job_type nvarchar(20) not null,
				job_id uniqueidentifier,
				maxtrans int,
				maxscans int,
				continuous bit,
				pollinginterval  bigint,
				retention bigint,
				threshold bigint,
				primary key (database_id, job_type)
			)
			
			set @action = N'sp_MS_marksystemobject ''msdb.dbo.cdc_jobs'''
			exec msdb.dbo.sp_MS_marksystemobject 'msdb.dbo.cdc_jobs'
		end

		
		-- Existing and orphaned job check
		
		select @old_job_id = job_id
		from msdb.dbo.cdc_jobs
		where database_id = db_id()
		and job_type = @job_type

		if @old_job_id is not null
		begin
			-- orphaned cdc_job entry, this occurs when a user manually drops a job
			if not exists ( select * from msdb.dbo.sysjobs where job_id = @old_job_id)
			begin
				set @action = N'delete from msdb.dbo.cdc_jobs'
				delete from msdb.dbo.cdc_jobs where database_id = db_id() and job_id = @old_job_id
			end	
			else -- job already exists (exit without error)
				goto FINISHED
		end

		
		-- Begin job addition
		
		select @job_name = N'cdc.' + db_name() + N'_' + @job_type
		select @valid_job = 0

		while @valid_job <> 1
		begin
			if exists (select * from msdb.dbo.sysjobs where name = @job_name)
			begin
				select @job_name = N'cdc.' + db_name() + N'_' + @job_type +
					N'.' + CONVERT(nvarchar(36), NEWID())
			end
			else
				select @valid_job = 1
		end

		select @category_name = case @job_type when N'capture' then N'REPL-LogReader'   else N'REPL-Checkup' end
		select @description   = case @job_type when N'capture' then N'CDC Log Scan Job' else N'CDC Cleanup Job' end
		select @databasename  = db_name()
		select @database_id   = db_id()
		select @server        = publishingservername()

		
		--  add job, use agent security for now
		
		set @action = N'msdb.dbo.sp_add_job'
		EXECUTE msdb.dbo.sp_add_job
			@job_name                   = @job_name,
			@enabled                    = 1,
			@start_step_id              = 1,
			@description                = @description,
			@category_name              = @category_name,
			@notify_level_eventlog      = 2, -- Write to event log on failure
			@owner_login_name		    = NULL,
			@job_id                     = @job_id OUTPUT

		set @action = N'insert into msdb.dbo.cdc_jobs'
		insert into msdb.dbo.cdc_jobs (database_id, job_id, pollinginterval, maxtrans, maxscans, continuous, job_type, retention, threshold)
			values (@database_id, @job_id, @pollinginterval, @maxtrans, @maxscans, @continuous, @job_type, @retention, @threshold)

		
		-- add job step
		
		set @step_id = 1
		if(@job_type = N'capture')
		begin
			select @command = N'RAISERROR(22801, 10, -1)'

			select @step_name = N'Starting Change Data Capture Collection Agent'
		
			set @action = N'sp_add_jobstep_internal'
			EXECUTE msdb.dbo.sp_add_jobstep_internal
				@job_id                = @job_id,
				@step_id               = @step_id,
				@step_name             = @step_name,
				@subsystem             = N'TSQL',
				@command               = @command,
				@cmdexec_success_code  = 0,
				@on_success_action     = 3,      -- 1 = Quit With Success, 2 = Quit With Failure, 3 = Goto Next Step, 4 = Goto Step
				@on_fail_action        = 3,      -- 1 = Quit With Success, 2 = Quit With Failure, 3 = Goto Next Step, 4 = Goto Step
				@server                = @server,
				@database_name         = @databasename,
				@database_user_name    = NULL, -- DBO
				@retry_attempts        = 10, -- LR Defaults
				@retry_interval        = 1,  -- LR Defaults
				@os_run_priority       = 0,  -- LR Defaults
				@flags = 4, -- 4 = Write TSQL step output to step history,
				@step_uid              = @job_step_uid OUTPUT
	
			set @step_id = 2
		end

		select @command = case @job_type when N'capture' then N'sys.sp_MScdc_capture_job'
					  else N'sys.sp_MScdc_cleanup_job' end

		select @step_name = case @job_type when N'capture' then N'Change Data Capture Collection Agent'
						else N'Change Data Capture Cleanup Agent' end
		
		set @action = N'sp_add_jobstep_internal'
		EXECUTE msdb.dbo.sp_add_jobstep_internal
			@job_id                = @job_id,
			@step_id               = @step_id,
			@step_name             = @step_name,
			@subsystem             = N'TSQL',
			@command               = @command,
			@cmdexec_success_code  = 0,
			@on_success_action     = 1,
			@on_fail_action        = 2,
			@server                = @server,
			@database_name         = @databasename,
			@database_user_name    = NULL, -- DBO
			@retry_attempts        = 10, -- LR Defaults
			@retry_interval        = 1,  -- LR Defaults
			@os_run_priority       = 0,  -- LR Defaults
			@flags = 4, -- 4 = Write TSQL step output to step history,
			@step_uid              = @job_step_uid OUTPUT

		
		-- add a job schedule
		
		select @schedule_name = case @job_type when N'capture' then N'CDC capture agent schedule.'
						else N'CDC cleanup agent schedule.' end

		-- Run the job continuously if capture job
		if (@job_type = N'capture')
		begin
			set @action = N'sp_add_jobschedule'
			EXECUTE @retval = msdb.dbo.sp_add_jobschedule
	    		@job_id                 = @job_id,
				@name                   = @schedule_name,
				@enabled                = 1,
				@freq_type              = 64,
				@freq_subday_type       = 1,
				@freq_subday_interval   = 1,
				@freq_relative_interval = 1,
				@freq_recurrence_factor = 1,
				@active_start_date      = 0,
				@active_end_date        = 0,
				@active_start_time      = 0,
				@active_end_time        = 0
		end		
		else
		-- Otherwise, schedule cleanup job to run once per day.
		begin
			set @action = N'sp_add_jobschedule'
			EXECUTE msdb.dbo.sp_add_jobschedule
	    		@job_id                 = @job_id,
				@name                   = @schedule_name,
				@enabled                = 1,
				@freq_type              = 4, -- run daily
				@freq_interval			= 1, -- every 1 days
				@freq_subday_type       = 1,
				@freq_subday_interval   = 1,
				@freq_relative_interval = 1,
				@freq_recurrence_factor = 1,
				@active_start_date      = 0,
				@active_end_date        = 0,
				@active_start_time      = 020000, -- at HHMMSS on a 24 hour clock = 2 AM
				@active_end_time        = 0
		end

		
		-- Define the server on which the job runs, for CDC it's always local
		
		set @action = N'sp_add_jobserver'
		EXECUTE msdb.dbo.sp_add_jobserver @job_id = @job_id

		
		-- Start the job, if required
		
		if @start_job = 1
		begin
			set @action = N'sp_start_job'
			EXECUTE msdb.dbo.sp_start_job @job_id = @job_id, @error_flag = 0
		end

FINISHED:

		commit tran
		
		return(0)

    END TRY

    BEGIN CATCH

		if @@trancount > @trancount
		begin
			if XACT_STATE() = 1
			begin
				rollback tran tr_sp_cdc_add_job
				commit tran
			end	
		end
		
		set @raise_error = ERROR_NUMBER()
		set @raise_message = ERROR_MESSAGE()
		
	END CATCH
	
	raiserror(22836, 16, -1, @db_name, @action, @raise_error, @raise_message)	

    return(1)
end

 
Last revision 2008RTM
See also

  sp_cdc_add_job (Procedure)
sp_MSdrop_cdc (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