November 27, 2008 at 6:11 am
hope you guys could help, i'm trying to create a script that would create a backup job for each database
when i perform a while loop statement it does create a job on the first database and gives out an error
Msg 14274, Level 16, State 1, Procedure sp_add_job, Line 132
Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server.
for the succeeding databases
here's my script
-- JobDB contains the ID and name of database
DECLARE @LoopCounter int, @dbasename varchar(25)
-- initialize variables
SELECT @LoopCounter = MIN(ID)
FROM JobsDB
SELECT @dbasename = DBname
FROM JobsDB where
ID = @LoopCounter
WHILE @LoopCounter <= (select max(ID) from JobsDB)
BEGIN
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Backups' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Backups'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Job1',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Job1',
@category_name=N'Database Backups',
@owner_login_name=N'Domain\SQL',
@notify_email_operator_name=N'SQL', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
SET @LoopCounter = @LoopCounter + 1
SELECT @dbasename = DBname
FROM JobsDB where
ID = @LoopCounter
END
RETURN
November 27, 2008 at 7:42 am
There are a couple of issues with you're script.
First of all in the while loop you should use < instead of <=
Second you have to create a unique jobname for each job. Now you would alway create a job with the name Job1 so once theJob1 is created the next try will fail.
Third you need to create a jobstep which actually executes the BACKUP statement. And don't forget about the schedules.
But if you're trying to backup all databases why don't you just create a Maintenance plan to backup all databases?
[font="Verdana"]Markus Bohse[/font]
November 27, 2008 at 8:07 am
i didn't copy and paste the whole script but it also has statements for creating job schedules, steps etc...
i created a db named JobsDB which contains an ID (auto-increment) and DBname
the while loop seems to be fine, i tested it without the Job statements
my plan was to have this script executed daily and if it finds out a new database added then it will create a job for it automatically, unfortunalely i'm still stuck on this part
when i actually ran the script it creates a job for the first DB and gives the error for the rest of the DBs, but if i ran the job again it would create a job for the second DB and an error again
say if i have 5 DBs, if i ran the script 5 times it would eventually create a job for each DB
November 27, 2008 at 8:33 am
JPster (11/27/2008)
when i actually ran the script it creates a job for the first DB and gives the error for the rest of the DBs, but if i ran the job again it would create a job for the second DB and an error againsay if i have 5 DBs, if i ran the script 5 times it would eventually create a job for each DB
The script gives you an error because of that statement:
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Job1'
Change it to
declare @JobName Nvarchar(20)
set @JobName = N'Job' + Cast(@LoopCounter as nvarchar(4))
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name = @JobName
November 27, 2008 at 8:56 am
thanks for the reply Vic.
i did try your script but it still gives me a problem, it will create the first job and have an error for the others.
if i ran the job again it will create a job for the 2nd database but will have an error on the rest.
below is my error
Msg 14261, Level 16, State 1, Procedure sp_verify_job, Line 56
The specified @name ('Job1') already exists.
Msg 14261, Level 16, State 1, Procedure sp_verify_job, Line 56
The specified @name ('Job2') already exists.
Msg 14274, Level 16, State 1, Procedure sp_add_job, Line 132
Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server.
Msg 14274, Level 16, State 1, Procedure sp_add_job, Line 132
Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server.
Msg 14274, Level 16, State 1, Procedure sp_add_job, Line 132
Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server.
this is the error i'm concerned about
Msg 14274, Level 16, State 1, Procedure sp_add_job, Line 132
Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server.
apparently when it tries to loop to create the next job that's the time i get the error message
November 27, 2008 at 9:15 am
I looked in the system procedure sp_add_job where the error comes from. you have an output variable @jobId.
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Job1',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Job1',
@category_name=N'Database Backups',
@owner_login_name=N'Domain\SQL',
@notify_email_operator_name=N'SQL', @job_id = @jobId OUTPUT
By the first call it is null, by the next one not.
sp_add_job checks for the @jobId:
IF (@job_id IS NULL)
BEGIN
-- Assign the GUID
SELECT @job_id = NEWID()
END
ELSE
BEGIN
-- A job ID has been provided, so check that the caller is SQLServerAgent (inserting an MSX job)
IF (PROGRAM_NAME() NOT LIKE N'SQLAgent%')
BEGIN
RAISERROR(14274, -1, -1)
RETURN(1) -- Failure
END
END
so set @jobId to Null before calling msdb.dbo.sp_add_job, this should do it
November 28, 2008 at 3:38 am
thanks Vic, it worked!! now i can continue with my script 🙂
again thanks a lot
October 13, 2009 at 3:41 am
Hi Newbie,
Can you please send me the full script of taking backups for all databases.
Thanks,
Nitin
April 29, 2010 at 4:57 am
Hi Guys
i had a similar problem whereby i needed to automatically drop and create a job to execute a stored proc and i got the MSX error.
in my drop statement i first go and retrieve the jobid for the job i want to drop, then i drop the job using the jobid.
and off course the next step is to create the job where the same jobid variable is used to retrieve the supposed new jobid.
so all i did was reset the jobid after dropping the job and voila, all works beautifully.
Thanks Vic.K for indicating to reset the jobid
October 19, 2010 at 6:23 am
I have the same error .. MSX.
I have just added the SET @jobid = NULL, and now, it work. 😀
Thanks Vic.K
December 18, 2015 at 9:09 am
Thank you!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply