Home Forums SQL Server 2005 Development Dynamically creating sql server job using SP and kicking off the job RE: Dynamically creating sql server job using SP and kicking off the job

  • May someone need it:

    create procedure test (@variable varchar (40)) as

    -- create dynamiclly command which job will execute. This allow you to pass parameter to job 🙂

    declare @command varchar (300)='exec database.dbo.test '+@variable

    -- create job

    exec msdb..sp_add_job

    @job_name =@variable,

    @enabled=1,

    @start_step_id=1,

    @delete_level=1 -- means job will delete itself after succes. If set to 3, will delete always

    -- code below allow you to pass any parameters wanted.

    exec msdb..sp_add_jobstep

    @job_name=@variable,

    @step_id=1,

    @step_name='exec',

    @command=@command

    exec msdb..sp_add_jobserver

    @job_name = @variable,

    @server_name = 'yourserver'

    exec msdb..sp_start_job

    @job_name=@variable

    Best regards!