none
Server Agent Jobs: Script job as create to file...

    Question

  • Hello,

     

    I would like to be able to script my server agent jobs to file via transact as in not relying on the Server Management Studio user interface.  Is there a stored procedure that I can use to accomplish this?

     

    For example, something like:

     

    dbo.sp_job_to_file 'job_id','disk_location'

     

    Thanks in advanced,

     

    CC

     

    -

     

    Runing SQL Server 2005 - Utilizing SQL Server Management Studio

     

     

    Tuesday, May 01, 2007 5:34 PM

Answers

  • Hello,

     

    There is not a straight-forward answer to your question, however here are some items you may find helpful:

    1. THE SQL Server SMO Library can be used to script Agent Jobs (which is the API that SSMS is using)
    2. MSDB is the containing database for the jobs and their tables/views
    3. My guess is there is that when you call the SMO Script() method of the job object it is polling the system tables and then generating the appropriate TSQL Scripts.

    Hope this helps,

    Derek

    Tuesday, May 01, 2007 7:37 PM
  • Hey Derek,  I thought you might be interested in seeing what I came up with.  Basically, I have a stored proc that creates a stored proc for each job under the server agent.  From there I can execute the proc to create the cooresponding agent job.  Some work remains on the scheduling but the jist of the task is here...

     

    use [DevDB]

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    --Job

    DECLARE @jobID nvarchar(40),@stepID nvarchar(40),@procName varchar (255),@jobName varchar (255), @command_job varchar(8000), @command_step varchar(8000),@command_end varchar(8000),@command_sched varchar(8000)

    DECLARE @job_name as varchar(255),@owner_login_name as varchar(255),@description as varchar(255),@category_name as varchar(255),@enabled as nvarchar(4)

    DECLARE @notify_level_email as nvarchar(4),@notify_level_page as nvarchar(4),@notify_level_netsend as nvarchar(4)

    DECLARE @notify_level_eventlog as nvarchar(4),@delete_level as nvarchar(4), @start_step_id nvarchar(4)

    --Job Steps

    DECLARE @step_name varchar(255),@command varchar(8000),@database_name varchar(255),@database_user_name varchar(255), @subsystem nvarchar(40)

    DECLARE @cmdexec_success_code nvarchar(2),@flags nvarchar(2),@retry_attempts nvarchar(2),@retry_interval nvarchar(2),@output_file_name varchar (255)

    DECLARE @on_success_step_id nvarchar(3),@on_success_action nvarchar(2),@on_fail_step_id nvarchar(2),@on_fail_action nvarchar(2)

    if EXISTS(select * from DevDB.INFORMATION_SCHEMA.TABLES where table_name='server_agent_jobs')

    BEGIN

    DROP TABLE DevDB..server_agent_jobs

    End

    --ignore multi-server jobs at this time

    select sj.job_id, name, '0' as executed into DevDB..server_agent_jobs from msdb..sysjobs sj inner join msdb..sysjobservers ss on ss.job_id = sj.job_id and ss.server_id = '0' order by sj.job_id

    WHILE EXISTS (select job_id from DevDB..server_agent_jobs where executed = '0')

    BEGIN

    --get working job_id and stored proc name

    SELECT top 1 @jobID = job_id, @procName = 'sp_create_server_agent_job_' + name, @jobName = name FROM DevDB..server_agent_jobs

    WHERE executed = '0'

    order by job_id

    --get job info from sysjobs

    select @job_name = a.name,@owner_login_name = b.name,@description = a.description,@category_name = c.name,@enabled = a.enabled,

    @notify_level_email = a.notify_level_email,@notify_level_page = a.notify_level_page,

    @notify_level_netsend = a.notify_level_netsend,@notify_level_eventlog = a.notify_level_eventlog,@delete_level = a.delete_level,

    @start_step_id = a.start_step_id

    FROM msdb..sysjobs a, master..syslogins b, msdb..syscategories c

    WHERE a.owner_sid = b.sid and a.category_id = c.category_id and job_id = @jobID

    --get job info from sysjobsteps

    if EXISTS(select * from DevDB.INFORMATION_SCHEMA.TABLES where table_name='server_agent_job_steps')

    BEGIN

    DROP TABLE DevDB..server_agent_job_steps

    End

    select *, '0' as executed into DevDB..server_agent_job_steps from msdb..sysjobsteps sjs where job_id = @jobID order by sjs.step_id

    if EXISTS(select * from DevDB.INFORMATION_SCHEMA.TABLES where table_name='server_agent_job_schedules')

    BEGIN

    DROP TABLE DevDB..server_agent_job_schedules

    End

    select *, '0' as executed into DevDB..server_agent_job_schedules from msdb..sysjobschedules sjs where job_id = @jobID order by sjs.schedule_id

    --drop proc if it exists before it's re-created

    if EXISTS(select * from DevDB.INFORMATION_SCHEMA.ROUTINES where routine_name = @procName)

    BEGIN

    exec('DROP PROCEDURE [' + @procName + ']')

    End

    SET CONCAT_NULL_YIELDS_NULL OFF

    select @command_job = 'CREATE Procedure [dbo].[' + @procName + '](

    @operator varchar(255)

    )

    AS

    BEGIN

    SET NOCOUNT OFF

    DECLARE @ReturnCode nvarchar (40), @jobID nvarchar (40)

    Begin Transaction

    --delete job if it already exists (by job name -> @jobName)

    if EXISTS(select * from msdb..sysjobs where name = ''' + @jobName + ''')

    begin

    EXECUTE msdb.dbo.sp_delete_job @job_name = ''' + @jobName + '''

    end

    --start creating the new job

    if @operator <> ''''

    begin

    EXECUTE @ReturnCode = msdb.dbo.sp_add_job

    @job_id = @jobID OUTPUT,

    @job_name = ''' + @job_name + ''',

    @owner_login_name = ''' + @owner_login_name + ''',

    @description = ''' + @description + ''',

    @category_name = ''' + @category_name + ''',

    @enabled = ''' + @enabled + ''',

    @notify_level_email = ''' + @notify_level_email + ''',

    @notify_level_page = ''' + @notify_level_page + ''',

    @notify_level_netsend = ''' + @notify_level_netsend + ''',

    @notify_level_eventlog = ''' + @notify_level_eventlog + ''',

    @delete_level = ''' + @delete_level + ''',

    @notify_email_operator_name = @operator

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    end

    else

    begin

    EXECUTE @ReturnCode = msdb.dbo.sp_add_job

    @job_id = @jobID OUTPUT,

    @job_name = ''' + @job_name + ''',

    @owner_login_name = ''' + @owner_login_name + ''',

    @description = ''' + @description + ''',

    @category_name = ''' + @category_name + ''',

    @enabled = ''' + @enabled + ''',

    @notify_level_email = ''' + @notify_level_email + ''',

    @notify_level_page = ''' + @notify_level_page + ''',

    @notify_level_netsend = ''' + @notify_level_netsend + ''',

    @notify_level_eventlog = ''' + @notify_level_eventlog + ''',

    @delete_level = ''' + @delete_level + '''

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    end

    --add job steps

    '

    WHILE EXISTS (select * from DevDB..server_agent_job_steps where executed = '0')

    BEGIN

    select top 1 @stepID = step_id from DevDB..server_agent_job_steps where executed = '0' order by step_id

    SELECT @step_name = a.step_name,@command = a.command, @database_name = a.database_name,@database_user_name = a.database_user_name,

    @subsystem = a.subsystem, @cmdexec_success_code = a.cmdexec_success_code, @flags = a.flags, @retry_attempts = a.retry_attempts,

    @retry_interval = a.retry_interval, @output_file_name = a.output_file_name, @on_success_step_id = a.on_success_step_id,

    @on_success_action = a.on_success_action, @on_fail_step_id = a.on_fail_step_id, @on_fail_action = a.on_fail_action

    FROM DevDB..server_agent_job_steps a where a.step_id = @stepID

    select @command_step = @command_step + 'EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep

    @job_id = @JobID,

    @step_id = ''' + @stepID + ''',

    @step_name = ''' + @step_name + ''',

    @command = ''' + REPLACE (@command,'''','''''') + ''',

    @database_name = ''' + @database_name + ''',

    @server = ''' + '' + ''',

    @database_user_name = ''' + @database_user_name + ''',

    @subsystem = ''' + @subsystem + ''',

    @cmdexec_success_code = ''' + @cmdexec_success_code + ''',

    @flags = ''' + @flags + ''',

    @retry_attempts = ''' + @retry_attempts + ''',

    @retry_interval = ''' + @retry_interval + ''',

    @output_file_name = ''' + @output_file_name + ''',

    @on_success_step_id = ''' + @on_success_step_id + ''',

    @on_success_action = ''' + @on_success_action + ''',

    @on_fail_step_id = ''' + @on_fail_step_id + ''',

    @on_fail_action = ''' + @on_fail_action + '''

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    '

    update DevDB..server_agent_job_steps set executed = '1' where step_id = @stepID

    END

    --update start step id from sysjobs

    select @command_step = @command_step + 'EXECUTE @ReturnCode = msdb.dbo.sp_update_job

    @job_id = @JobID,

    @start_step_id = ''' + @start_step_id + '''

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    '

    select @command_end = 'EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver

    @job_id = @JobID,

    @server_name = N''(local)''

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    '

    select @command_end = @command_end + '

    COMMIT TRANSACTION

    GOTO THE_END

    QuitWithRollback:

    print ''job failed''

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

    THE_END:

    END'

    --print (@command_job + @command_step + @command_sched + @command_end)

    SET CONCAT_NULL_YIELDS_NULL ON

    execute (@command_job + @command_step + @command_sched + @command_end)

    update DevDB..server_agent_jobs set executed = '1' where DevDB..server_agent_jobs.job_id = @jobID

    select @command_step = ''

    END

    Tuesday, May 08, 2007 5:39 PM

All replies

  • Hello,

     

    There is not a straight-forward answer to your question, however here are some items you may find helpful:

    1. THE SQL Server SMO Library can be used to script Agent Jobs (which is the API that SSMS is using)
    2. MSDB is the containing database for the jobs and their tables/views
    3. My guess is there is that when you call the SMO Script() method of the job object it is polling the system tables and then generating the appropriate TSQL Scripts.

    Hope this helps,

    Derek

    Tuesday, May 01, 2007 7:37 PM
  • Thanks Derek,

     

    I have reviewed the system tables containing the jobs as well and reviewed the stored procedures that actually create jobs, and truth be told I could probably manually hammer out my own proc to accomplish what I need.  I was hoping there was a way to see exactly what is executed when the task is ran via Management Studio as to save myself some work Smile

     

    I just seems like a waste of time to write a stored proc to do what can be done via the user interface...

     

    CC

     

    -

    Tuesday, May 01, 2007 8:12 PM
  • run profiler when you gen job scripts via SSMS and see what you get? would be interesting to see Smile
    Wednesday, May 02, 2007 5:19 PM
  • Hey Derek,  I thought you might be interested in seeing what I came up with.  Basically, I have a stored proc that creates a stored proc for each job under the server agent.  From there I can execute the proc to create the cooresponding agent job.  Some work remains on the scheduling but the jist of the task is here...

     

    use [DevDB]

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    --Job

    DECLARE @jobID nvarchar(40),@stepID nvarchar(40),@procName varchar (255),@jobName varchar (255), @command_job varchar(8000), @command_step varchar(8000),@command_end varchar(8000),@command_sched varchar(8000)

    DECLARE @job_name as varchar(255),@owner_login_name as varchar(255),@description as varchar(255),@category_name as varchar(255),@enabled as nvarchar(4)

    DECLARE @notify_level_email as nvarchar(4),@notify_level_page as nvarchar(4),@notify_level_netsend as nvarchar(4)

    DECLARE @notify_level_eventlog as nvarchar(4),@delete_level as nvarchar(4), @start_step_id nvarchar(4)

    --Job Steps

    DECLARE @step_name varchar(255),@command varchar(8000),@database_name varchar(255),@database_user_name varchar(255), @subsystem nvarchar(40)

    DECLARE @cmdexec_success_code nvarchar(2),@flags nvarchar(2),@retry_attempts nvarchar(2),@retry_interval nvarchar(2),@output_file_name varchar (255)

    DECLARE @on_success_step_id nvarchar(3),@on_success_action nvarchar(2),@on_fail_step_id nvarchar(2),@on_fail_action nvarchar(2)

    if EXISTS(select * from DevDB.INFORMATION_SCHEMA.TABLES where table_name='server_agent_jobs')

    BEGIN

    DROP TABLE DevDB..server_agent_jobs

    End

    --ignore multi-server jobs at this time

    select sj.job_id, name, '0' as executed into DevDB..server_agent_jobs from msdb..sysjobs sj inner join msdb..sysjobservers ss on ss.job_id = sj.job_id and ss.server_id = '0' order by sj.job_id

    WHILE EXISTS (select job_id from DevDB..server_agent_jobs where executed = '0')

    BEGIN

    --get working job_id and stored proc name

    SELECT top 1 @jobID = job_id, @procName = 'sp_create_server_agent_job_' + name, @jobName = name FROM DevDB..server_agent_jobs

    WHERE executed = '0'

    order by job_id

    --get job info from sysjobs

    select @job_name = a.name,@owner_login_name = b.name,@description = a.description,@category_name = c.name,@enabled = a.enabled,

    @notify_level_email = a.notify_level_email,@notify_level_page = a.notify_level_page,

    @notify_level_netsend = a.notify_level_netsend,@notify_level_eventlog = a.notify_level_eventlog,@delete_level = a.delete_level,

    @start_step_id = a.start_step_id

    FROM msdb..sysjobs a, master..syslogins b, msdb..syscategories c

    WHERE a.owner_sid = b.sid and a.category_id = c.category_id and job_id = @jobID

    --get job info from sysjobsteps

    if EXISTS(select * from DevDB.INFORMATION_SCHEMA.TABLES where table_name='server_agent_job_steps')

    BEGIN

    DROP TABLE DevDB..server_agent_job_steps

    End

    select *, '0' as executed into DevDB..server_agent_job_steps from msdb..sysjobsteps sjs where job_id = @jobID order by sjs.step_id

    if EXISTS(select * from DevDB.INFORMATION_SCHEMA.TABLES where table_name='server_agent_job_schedules')

    BEGIN

    DROP TABLE DevDB..server_agent_job_schedules

    End

    select *, '0' as executed into DevDB..server_agent_job_schedules from msdb..sysjobschedules sjs where job_id = @jobID order by sjs.schedule_id

    --drop proc if it exists before it's re-created

    if EXISTS(select * from DevDB.INFORMATION_SCHEMA.ROUTINES where routine_name = @procName)

    BEGIN

    exec('DROP PROCEDURE [' + @procName + ']')

    End

    SET CONCAT_NULL_YIELDS_NULL OFF

    select @command_job = 'CREATE Procedure [dbo].[' + @procName + '](

    @operator varchar(255)

    )

    AS

    BEGIN

    SET NOCOUNT OFF

    DECLARE @ReturnCode nvarchar (40), @jobID nvarchar (40)

    Begin Transaction

    --delete job if it already exists (by job name -> @jobName)

    if EXISTS(select * from msdb..sysjobs where name = ''' + @jobName + ''')

    begin

    EXECUTE msdb.dbo.sp_delete_job @job_name = ''' + @jobName + '''

    end

    --start creating the new job

    if @operator <> ''''

    begin

    EXECUTE @ReturnCode = msdb.dbo.sp_add_job

    @job_id = @jobID OUTPUT,

    @job_name = ''' + @job_name + ''',

    @owner_login_name = ''' + @owner_login_name + ''',

    @description = ''' + @description + ''',

    @category_name = ''' + @category_name + ''',

    @enabled = ''' + @enabled + ''',

    @notify_level_email = ''' + @notify_level_email + ''',

    @notify_level_page = ''' + @notify_level_page + ''',

    @notify_level_netsend = ''' + @notify_level_netsend + ''',

    @notify_level_eventlog = ''' + @notify_level_eventlog + ''',

    @delete_level = ''' + @delete_level + ''',

    @notify_email_operator_name = @operator

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    end

    else

    begin

    EXECUTE @ReturnCode = msdb.dbo.sp_add_job

    @job_id = @jobID OUTPUT,

    @job_name = ''' + @job_name + ''',

    @owner_login_name = ''' + @owner_login_name + ''',

    @description = ''' + @description + ''',

    @category_name = ''' + @category_name + ''',

    @enabled = ''' + @enabled + ''',

    @notify_level_email = ''' + @notify_level_email + ''',

    @notify_level_page = ''' + @notify_level_page + ''',

    @notify_level_netsend = ''' + @notify_level_netsend + ''',

    @notify_level_eventlog = ''' + @notify_level_eventlog + ''',

    @delete_level = ''' + @delete_level + '''

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    end

    --add job steps

    '

    WHILE EXISTS (select * from DevDB..server_agent_job_steps where executed = '0')

    BEGIN

    select top 1 @stepID = step_id from DevDB..server_agent_job_steps where executed = '0' order by step_id

    SELECT @step_name = a.step_name,@command = a.command, @database_name = a.database_name,@database_user_name = a.database_user_name,

    @subsystem = a.subsystem, @cmdexec_success_code = a.cmdexec_success_code, @flags = a.flags, @retry_attempts = a.retry_attempts,

    @retry_interval = a.retry_interval, @output_file_name = a.output_file_name, @on_success_step_id = a.on_success_step_id,

    @on_success_action = a.on_success_action, @on_fail_step_id = a.on_fail_step_id, @on_fail_action = a.on_fail_action

    FROM DevDB..server_agent_job_steps a where a.step_id = @stepID

    select @command_step = @command_step + 'EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep

    @job_id = @JobID,

    @step_id = ''' + @stepID + ''',

    @step_name = ''' + @step_name + ''',

    @command = ''' + REPLACE (@command,'''','''''') + ''',

    @database_name = ''' + @database_name + ''',

    @server = ''' + '' + ''',

    @database_user_name = ''' + @database_user_name + ''',

    @subsystem = ''' + @subsystem + ''',

    @cmdexec_success_code = ''' + @cmdexec_success_code + ''',

    @flags = ''' + @flags + ''',

    @retry_attempts = ''' + @retry_attempts + ''',

    @retry_interval = ''' + @retry_interval + ''',

    @output_file_name = ''' + @output_file_name + ''',

    @on_success_step_id = ''' + @on_success_step_id + ''',

    @on_success_action = ''' + @on_success_action + ''',

    @on_fail_step_id = ''' + @on_fail_step_id + ''',

    @on_fail_action = ''' + @on_fail_action + '''

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    '

    update DevDB..server_agent_job_steps set executed = '1' where step_id = @stepID

    END

    --update start step id from sysjobs

    select @command_step = @command_step + 'EXECUTE @ReturnCode = msdb.dbo.sp_update_job

    @job_id = @JobID,

    @start_step_id = ''' + @start_step_id + '''

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    '

    select @command_end = 'EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver

    @job_id = @JobID,

    @server_name = N''(local)''

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    '

    select @command_end = @command_end + '

    COMMIT TRANSACTION

    GOTO THE_END

    QuitWithRollback:

    print ''job failed''

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

    THE_END:

    END'

    --print (@command_job + @command_step + @command_sched + @command_end)

    SET CONCAT_NULL_YIELDS_NULL ON

    execute (@command_job + @command_step + @command_sched + @command_end)

    update DevDB..server_agent_jobs set executed = '1' where DevDB..server_agent_jobs.job_id = @jobID

    select @command_step = ''

    END

    Tuesday, May 08, 2007 5:39 PM
  • NICE
    Tuesday, May 08, 2007 6:32 PM
  • TY,  it works well with a mirrored server in the event of a fail over.

     

    CC

     

    -

    Tuesday, May 08, 2007 6:40 PM
  • Thanks ;)
    Monday, May 19, 2014 2:57 PM