Tip: don't forget sp_add_jobserver

  • netmikem

    Hall of Fame

    Points: 3998

    Hello.

    I thought I would pass along this little tidbit of information I stumbled across today.

    I was attempting to create a backup job using T-SQL (i.e. sp_add_job, sp_add_jobstep, and sp_add_jobschedule). The job was being created okay except that it had a Runnable status of "No (Add target servers to the job)".

    If I tried to run this job from Enterprise Manager, I would get the following error:

    Error 14256: Cannot start job 'job name' <job_id> because it does not have any job server(s) defined.

    I even checked the various system tables in the MSDB database (e.g. sysjobs, sysjobsteps) to see if there was anything missing for this job. I couldn't see anything obvious.

    I found that the problem went away if I went into the job properties, poked around a little, and then clicked Apply.

    I then decided to run the 'Generate SQL Script' task on the job (after I had performed the Apply step as described previously). I then noticed a call to sp_add_jobserver. I looked it up in SQL BOL and once I got the syntax, I added it to my job creation script. Voila!

    I guess this stored procedure is required when creating jobs via T-SQL.

    I am adding this to the forum in case somebody else has this same problem. I didn't have much luck researching this on the Microsoft Knowledge Base.

    Regards,

    - Mike

    Whenever I tried to start the job,

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716562

    Thanks for the info. Wawnt to write this up as an aritle with a little more detail?

    Steve Jones

    sjones@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/sjones

    The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/

    http://www.dkranch.net

  • netmikem

    Hall of Fame

    Points: 3998

    Hi Steve.

    Sure, I can do this. Is there a procedure for submitting articles?

    - Mike

  • AspiringGeek

    Valued Member

    Points: 59

    Exactly what I needed! 

    BTW, I used this same technique to discover DBCC ShowFileStats.

    I'm unsure how I ever lived without either one of these statements.  Thanks!

  • sholliday

    SSCrazy

    Points: 2099

    THANK YOU !! FOR POSTING THIS TIP. 

    I was in the same boat, thank goodness for "exact phrase" google searches.

    Here is my code (for future readers)

    FYI, EXEC dbo.uspJobScheduleExists is my own code for checking to see if a jobschedule exists, I only wrote this because I didn't have access to some of the job related sysobjects.  But I was able to run the sp_job_xxxxxxxxxxxx scripts.

    I'm still fine tuning my code (esp for the time creator on the jobschedule), but this will get the point across:

      Do NOT add "Use msdb" to the script.  Run it against "mydatabase" , as in " Use mydatabase" 

    --start tsql

    if exists (select * from sysobjects

    where id = object_id('dbo.uspJobScheduleExists') and sysstat & 0xf = 4)

    drop procedure dbo.uspJobScheduleExists

    GO

    CREATE Procedure

    dbo.uspJobScheduleExists

    ( @jobName

    varchar(128) = null , @jobschedulename varchar (128) , @doesExist bit output )

    AS

    /*

    There was an issue getting information with a bad JobScheduleName (it threw an error)

    This is a friendlier way to see if the JobSchedule(name) already exists.

    A bad JobName will throw an error

    */

    SET NOCOUNT ON

     

    declare

    @nullCheck int

     

    create table

    #tempSchedule

    (

    schedule_id

    int,

    schedule_name

    varchar(64) ,

    enabled

    bit,

    freq_type

    smallint ,

    freq_interval

    smallint ,

    freq_subday_type

    smallint ,

    freq_subday_interval

    smallint ,

    freq_relative_interval

    smallint ,

    freq_recurrence_factor

    smallint ,

    active_start_date

    int ,active_end_date int ,

    active_start_time

    int ,active_end_time int ,

    date_created

    datetime ,

    schedule_description

    varchar(1024) ,

    next_run_date

    int ,next_run_time int

    )

     

    --not used----declare

    @hres int

    INSERT INTO

    #tempSchedule

    EXEC

    msdb.dbo.sp_help_jobschedule @job_name = @jobName

    --select * from #tempSchedule

    select

    @nullCheck = schedule_id from #tempSchedule where UPPER(schedule_name) = UPPER(@jobschedulename)

     

    drop table

    #tempSchedule

    if

    @nullCheck IS NOT NULL

    BEGIN

    select @doesExist = 1

    return

    END

    select

    @doesExist = 0

     

     

    SET NOCOUNT OFF

    GO

     

    -------------------- Now the code.. you only need to run the code above one time

    ---- below is the code you can run more than once, if you edit the info

    declare @jobName varchar(128)

    declare @jobStepName varchar(128)

    declare @jobScheduleName varchar(128)

    select @jobName    = 'MyJobRule0001'

    select @jobStepName   = 'JobStep1ForMyJobRule0001'

    select @jobScheduleName  = 'JobSchedule1ForMyJobRule0001'

    declare @serverName varchar(128)

    select @serverName = CONVERT(varchar(128) ,  SERVERPROPERTY('servername') )

    print @serverName

    declare @dbName varchar(128)

    select @dbName = DB_NAME()

    EXEC msdb.dbo.sp_add_job @job_name = @jobName,

        @enabled = 1,

        @description = @jobName,

        @owner_login_name = 'sa'

     

    EXEC msdb.dbo.sp_add_jobstep @job_name = @jobName,

       @step_name = @jobStepName,

       @subsystem = 'TSQL',

     @database_name = @dbName ,

       @command = 'EXEC dbo.uspMyStoredProcedure'

     

    EXEC msdb.dbo.sp_add_jobserver @job_name = @jobName,

       @server_name = @serverName

     

     

    declare @now datetime

    select @now = GETDATE()

    Select @now = DATEADD(s , 10 , @now)

    print @now

    --select DATEPART(s , @now)

    --select DATEPART(hh , @now)

    --select DATEPART(mi , @now)

    declare @hour varchar(2)

    declare @minute varchar(2)

    declare @second varchar(2)

    declare @hourint int

    declare @minuteint int

    declare @secondint int

    select @hourint= DATEPART(hh , @now)

    if @hourint < 10

     BEGIN

      select @hour = '0' + convert(varchar(1) , @hourint )

     END

    else

     BEGIN

      select @hour =  convert(varchar(2) , @hourint )

     END

    select @minuteint= DATEPART(mi , @now)

    if @minuteint < 10

     BEGIN

      select @minute = '0' + convert(varchar(1) , @minuteint )

     END

    else

     BEGIN

      select @minute = convert(varchar(2) , @minuteint )

     END

    select @secondint= DATEPART(s , @now)

    if @secondint < 10

     BEGIN

      select @second = '0' + convert(varchar(1) , @secondint )

     END

    else

     BEGIN

      select @second = convert(varchar(2) , @secondint )

     END

    print '*' + @hour + '*'

    print '*' + @minute + '*'

    print '*' + @second + '*'

    declare @starttime varchar(12)

    select @starttime = @hour + @minute + @second

    print @starttime

    declare @alreadyExists bit

    EXEC dbo.uspJobScheduleExists @jobName , @jobScheduleName  , @alreadyExists output

    if @alreadyExists = 0 --FALSE

     BEGIN

      EXEC msdb.dbo.sp_add_jobschedule

       @job_name = @jobName,

       @name = @jobScheduleName,

       @enabled = 1,

       @freq_type = 1 ,

       @freq_interval = 1  , --once

       @active_start_time = @starttime--'153000' --(3:30 pm) 24hr HHMMSS.

     END

    else

     BEGIN

      EXEC msdb.dbo.sp_update_jobschedule

       @job_name = @jobName,

       @name = @jobScheduleName,

       @enabled = 1,

       @freq_type = 1 ,

       @freq_interval = 1  , --once

       @active_start_time = @starttime--'153000' --(3:30 pm) 24hr HHMMSS.

     END

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply