Tip: don't forget sp_add_jobserver
-
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,
-
Thanks for the info. Wawnt to write this up as an aritle with a little more detail?
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
-
Hi Steve.
Sure, I can do this. Is there a procedure for submitting articles?
- Mike
-
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!
-
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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply