USP_BUSINESSPROCESSJOBSCHEDULE_ADD

Creates a SQL Server job for a business process.
Parameters

Parameter	Parameter Type	Mode
@JOBNAME	nvarchar(100)	IN
@DESCRIPTION	nvarchar(1000)	IN
@PROXYNAME	nvarchar(128)	IN
@COMMAND	nvarchar(4000)	IN
@CREATESCHEDULE	bit	IN
@ENABLESCHEDULE	bit	IN
@FREQENCYTYPE	int	IN
@FREQUENCYINTERVAL	int	IN
@FREQUENCYSUBDAYTYPE	int	IN
@FREQUENCYSUBDAYINTERVAL	int	IN
@FREQUENCYRELATIVEINTERVAL	int	IN
@FREQUENCYRECURRENCEFACTOR	int	IN
@ACTIVESTARTDATE	char(8)	IN
@ACTIVEENDDATE	char(8)	IN
@ACTIVESTARTTIME	char(6)	IN
@ACTIVEENDTIME	char(6)	IN
@SCHEDULEID	int	INOUT
Definition

Copy


CREATE procedure dbo.[USP_BUSINESSPROCESSJOBSCHEDULE_ADD]
(
  @JOBNAME nvarchar(100),
  @DESCRIPTION nvarchar(1000),
  @PROXYNAME nvarchar(128),
  @COMMAND nvarchar(4000),
  @CREATESCHEDULE bit = 0,
  @ENABLESCHEDULE bit = 1,
  @FREQENCYTYPE int = 0,
  @FREQUENCYINTERVAL int = 1,
  @FREQUENCYSUBDAYTYPE int = 0,
  @FREQUENCYSUBDAYINTERVAL int = 0,
  @FREQUENCYRELATIVEINTERVAL int = 0,
  @FREQUENCYRECURRENCEFACTOR int = 0,
  @ACTIVESTARTDATE char(8) = null,
  @ACTIVEENDDATE char(8) = null,
  @ACTIVESTARTTIME char(6) = null,
  @ACTIVEENDTIME char(6) = null,
  @SCHEDULEID integer = null output
)
as begin
  set nocount on;

  declare @OWNERNAME nvarchar(256) = suser_sname();
  declare @RESULT integer = 0;
  declare @JOBID binary(16);
  declare @SUCCESS bit = 1;

  declare @SUBSYS as nvarchar(40) = N'PowerShell';
  declare @STEPDBNAME as nvarchar(25);

  if @PROXYNAME like '% ActiveX %'
    begin
      set @SUBSYS = N'ActiveScripting';
      set @STEPDBNAME = N'VBScript';
    end

  begin transaction;

  if not exists (select name from msdb.dbo.syscategories where name = N'Blackbaud Business Process Jobs' and category_class = 1) begin
    exec @RESULT = msdb.dbo.sp_add_category
                     @class = N'JOB',
                     @type = N'LOCAL',
                     @name = N'Blackbaud Business Process Jobs';

    if @@ERROR <> 0 or @RESULT <> 0 set @SUCCESS = 0;
  end

  if @SUCCESS = 1 begin
    exec @RESULT =  msdb.dbo.sp_add_job
                      @job_name = @JOBNAME,
                      @enabled = 1,
                      @notify_level_eventlog = 0,
                      @notify_level_email = 0,
                      @notify_level_netsend = 0,
                      @notify_level_page = 0,
                      @delete_level = 0,
                      @description = @DESCRIPTION,
                      @category_name = N'Blackbaud Business Process Jobs',
                      @owner_login_name = @OWNERNAME,
                      @job_id = @JOBID output;

    if @@ERROR <> 0 or @RESULT <> 0 set @SUCCESS = 0;
  end

  if @SUCCESS = 1 begin
    exec @RESULT = msdb.dbo.sp_add_jobstep
                     @job_id = @JOBID,
                     @step_name = N'Business process invoke',
                     @step_id = 1,
                     @cmdexec_success_code = 0,
                     @on_success_action = 1,
                     @on_success_step_id = 0,
                     @on_fail_action = 2,
                     @on_fail_step_id = 0,
                     @retry_attempts = 0,
                     @retry_interval = 0,
                     @os_run_priority = 0,
                     @subsystem = @SUBSYS,
                     @command = @COMMAND,
                     @database_name = @STEPDBNAME,
                     @flags = 0,
                     @proxy_name = @PROXYNAME;

    if @@ERROR <> 0 or @RESULT <> 0 set @SUCCESS = 0;
  end

  if @SUCCESS = 1 begin
    exec @RESULT = msdb.dbo.sp_update_job
                     @job_id = @JOBID,
                     @start_step_id = 1;

    if @@ERROR <> 0 or @RESULT <> 0 set @SUCCESS = 0;
  end

  if @SUCCESS = 1 and @CREATESCHEDULE = 1 begin
    exec @RESULT = msdb.dbo.sp_add_jobschedule
                     @job_id = @JOBID,
                     @name = N'Business process schedule',
                     @enabled = @ENABLESCHEDULE,
                     @freq_type = @FREQENCYTYPE,
                     @freq_interval = @FREQUENCYINTERVAL,
                     @freq_subday_type = @FREQUENCYSUBDAYTYPE,
                     @freq_subday_interval = @FREQUENCYSUBDAYINTERVAL,
                     @freq_relative_interval = @FREQUENCYRELATIVEINTERVAL,
                     @freq_recurrence_factor = @FREQUENCYRECURRENCEFACTOR,
                     @active_start_date = @ACTIVESTARTDATE,
                     @active_end_date = @ACTIVEENDDATE,
                     @active_start_time = @ACTIVESTARTTIME,
                     @active_end_time = @ACTIVEENDTIME,
                     @schedule_id = @SCHEDULEID output;

    if @@ERROR <> 0 or @RESULT <> 0 set @SUCCESS = 0;
  end

  if @SUCCESS = 1 begin
    exec @RESULT = msdb.dbo.sp_add_jobserver
                     @job_id = @JOBID,
                     @server_name = N'(local)';

    if @@ERROR <> 0 or @RESULT <> 0 set @SUCCESS = 0;
  end

  if @SUCCESS = 1 begin
    commit transaction;
    return 0;
  end else begin
    if @@TRANCOUNT > 0 rollback transaction;
    return 1;
  end
end