Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSprocesslogshippingjob

  No additional text.


Syntax
create procedure sys.sp_MSprocesslogshippingjob
(
    @type tinyint -- 1 = backup, 2 = copy, 3 = restore, 4 = alert
    ,@mode tinyint -- 1 = create, 2 = drop
    ,@jobid uniqueidentifier = NULL output -- for create jobs it returns the new job, for drop jobs it specifies the job
    ,@job_name sysname = NULL -- cannot be NULL when creating
    ,@description nvarchar(512) = NULL -- cannot be NULL when creating
    ,@command nvarchar(3200) = NULL -- cannot be NULL when creating
    ,@overwrite bit = 0
)
as
begin
    set nocount on
    declare @retcode int
        ,@category sysname
        ,@category_id int
        ,@stepname sysname
        ,@subsystem sysname
        ,@enabled tinyint
        ,@schedule_name sysname
        ,@schedule_id int
        ,@sa_login sysname

    SELECT @sa_login = SUSER_SNAME(0x01)

    
    -- check to make sure the server has not been dropped
    
    if @@servername is null
    begin
         raiserror(32053, 16, 1)
         return 1
    end

    
    -- validate @type and @mode
    
    if (@type not in (1,2,3,4)) or (@mode not in (1,2))
        return 1
    
    -- process based on @mode
    
    if (@mode = 1)
    begin
        
        -- @mode is create - see if job exists with same name
        
        select @jobid = job_id
            ,@category_id = category_id
        from msdb.dbo.sysjobs_view
        where name = @job_name
        if (@jobid is not null)
        begin
            
            -- Job with same name exists
            -- If this is a logshipping job - we will drop it before creating
            -- If this is not a logshipping job - return error (if override is not set)
            
            if (@category_id != 6)
            begin
                if (@overwrite = 0)
                begin
                    raiserror(32022, 16, 1, @job_name)
                    return 1
                end
            end
        end
    end
    else
    begin
        
        -- @mode is drop - @jobid cannot be null
        
        if (@jobid is null)
            return 1
    end
    
    -- if @jobid is specified, we need to drop it first
    
    if (@jobid is not null)
    begin
        -- ignore errors during job deletion - the job may not exist
        begin try
        exec msdb.dbo.sp_delete_job @job_id = @jobid
        end try
        begin catch
            select @retcode = 0
        end catch
        select @jobid = null
    end
    
    -- Proceed if we are creating
    
    if (@mode = 2)
        return
    
    -- initialize based on type
    
    if (@job_name is null or @description is null or @command is null)
        return 1
    select @category = N'Log Shipping'
            ,@subsystem = case when (@type in (1,2,3)) then N'CMDEXEC' else N'TSQL' end
            ,@stepname = case when (@type = 1) then  isnull(formatmessage(32004), N'Logshipping backup log job step.')
                                        when (@type = 2) then  isnull(formatmessage(32005), N'Logshipping copy job step.')
                                        when (@type = 3) then  isnull(formatmessage(32006), N'Logshipping restore job step.')
                                        else  isnull(formatmessage(32020), N'Logshipping alert job step.') end
            ,@enabled = case when (@type in (1,2,3)) then 0 else 1 end
    
    -- Add job
    
    exec @retcode = msdb.dbo.sp_add_job @job_name=@job_name
                                ,@enabled=@enabled
                                ,@notify_level_eventlog=0
                                ,@notify_level_email=0
                                ,@notify_level_netsend=0
                                ,@notify_level_page=0
                                ,@delete_level=0
                                ,@description=@description
                                ,@category_name=@category
                                ,@owner_login_name=@sa_login
                                ,@job_id = @jobid output
    if (@@error != 0 OR @retcode != 0)
        return 1
    
    -- Add job step
    
    exec @retcode = msdb.dbo.sp_add_jobstep @job_id=@jobid
                                ,@step_name=@stepname
                                ,@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=@subsystem
                                ,@command=@command
                                ,@flags=32 -- avoid output truncation
    if (@@error != 0 OR @retcode != 0)
        return 1
    exec @retcode = msdb.dbo.sp_update_job @job_id = @jobid, @start_step_id = 1
    if (@@error != 0 OR @retcode != 0)
        return 1
    -- Do we need this ?
    exec msdb.dbo.sp_add_jobserver @job_id = @jobid
    
    -- special processing for alert job
    
    if (@type = 4)
    begin
        
        -- check if schedule exists
        
        select @schedule_name = isnull(formatmessage(32021), N'Log shipping alert job schedule.')
        select @schedule_id = schedule_id
        from msdb.dbo.sysschedules_localserver_view
        where name = @schedule_name
        if (@schedule_id is null)
        begin
            
            -- add the schedule
            
            exec @retcode = msdb.dbo.sp_add_schedule @schedule_name = @schedule_name
                                ,@freq_type = 4 -- daily
                                ,@freq_interval=1 -- everyday
                                ,@freq_subday_type = 4 -- minutes
                                ,@freq_subday_interval = 2 -- every 2 minutes
                                ,@owner_login_name=@sa_login
                                ,@schedule_id = @schedule_id output
            if (@@error != 0 OR @retcode != 0)
                return 1
        end
        
        -- attach the schedule
        
        exec @retcode = msdb.dbo.sp_attach_schedule @job_id = @jobid, @schedule_id = @schedule_id
        if (@@error != 0 OR @retcode != 0)
            return 1
    end
    
    -- all done
    
    return 0
end

 
Last revision 2008RTM
See also

  sp_add_log_shipping_alert_job_internal (Procedure)
sp_add_log_shipping_primary_database (Procedure)
sp_add_log_shipping_secondary_primary (Procedure)
sp_delete_log_shipping_alert_job_internal (Procedure)
sp_delete_log_shipping_primary_database (Procedure)
sp_delete_log_shipping_secondary_primary (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