Hi
I do have a full table but certain "sys admins" have already added some "important - must have" jobs.
Therefore I am doing this one by one at the moment which is working apart from setting the Target Server option.
I am trying to find out which table this is in so I can set it but I haven't had much luck so far.
The job below creates the job ok apart from this one thing. Which errors with "You must specify the servers on which this multi server job will execute"
I only want it to run on the local server. Which is an unticked box. I think it should be defaulted to on. But I cannot find the code to add it in. I am already using @@servername when I insert the new job records which I thought maybe would have triggered this.
I am also constantly getting FK errors when inserting the schedule records and no schedule records ever get added so I have to do them manually - which isn't a problem at the moment considering I have to set this TargetServer option manually anyway.
This is the code I am using - I have been constantly coding since 3:00AM BST this morning over 18 hours now so please excuse any obvious mistakes or issues. I am supposed to be off work due to Cellulitus on my swollen leg as well but I am constantly called on to do this work - nightmare!!!!
Let me know what you can see wrong with this code and how I can insert this TargetServer option so its always local server. I thought maybe it was to do with old server_id records somewhere in the backup table.
The debug is under the code
DECLARE @JobID UNIQUEIDENTIFIER
SELECT @JobID = job_id FROM msdb_from_neptunium.dbo.sysjobs WHERE NAME='Nightly Job'
PRINT @JobID
-- SELECT Name FROM msdb_from_neptunium.dbo.sysjobs WHERE Name NOT IN(SELECT Name FROM msdb.dbo.sysjobs)
INSERT msdb.dbo.sysjobs
SELECT * FROM msdb_from_neptunium.dbo.sysjobs
WHERE job_id=@JobID
INSERT msdb.dbo.sysjobsteps
SELECT * FROM msdb_from_neptunium.dbo.sysjobsteps
WHERE job_id=@JobID
--select top 1 * from msdb_from_neptunium.dbo.sysjobhistory select @@servername
SET IDENTITY_INSERT msdb.dbo.sysjobhistory ON
INSERT msdb.dbo.sysjobhistory
(instance_id,job_id,step_id,step_name,sql_message_id,sql_severity,
[message],run_status,run_date,run_time,run_duration,operator_id_emailed,
operator_id_netsent,operator_id_paged,retries_attempted,[server])
SELECT
instance_id,job_id,step_id,step_name,sql_message_id,sql_severity,
[message],run_status,run_date,run_time,run_duration,operator_id_emailed,
operator_id_netsent,operator_id_paged,retries_attempted,@@servername
FROM msdb_from_neptunium.dbo.sysjobhistory
WHERE job_id=@JobID
SET IDENTITY_INSERT msdb.dbo.sysjobhistory OFF
/*
select * from msdb.dbo.sysjobhistory
select * from msdb.dbo.sysjobsteps
select * from msdb.dbo.sysjobstepslogs
Select * from msdb.dbo.sysjobschedules
Select * from msdb.dbo.sysschedules
select * from msdb.dbo.sysjobschedules
select * from msdb_from_neptunium.dbo.sysjobschedules
where Schedule_id in(SELECT Schedule_id FROM msdb.dbo.sysjobschedules)
*/
PRINT 'DO Job Schedules' -- Select * from msdb.dbo.sysjobschedules where job_id='F5A0ECCB-4E0A-4D85-9A19-0867CA43F0EA'
IF EXISTS(SELECT Job_ID FROM msdb.dbo.sysjobschedules WHERE Job_ID=@JobID)
PRINT 'This Job exists in sysjobschedules'
ELSE
PRINT 'This Job does not exist in sysjobschedules'
DECLARE @ScheduleID INT
SELECT @ScheduleID = Schedule_ID
FROM msdb_from_neptunium.dbo.sysjobschedules
WHERE job_id = @JobID
PRINT 'ScheduleID = ' +CAST(@ScheduleID as varchar)
-- try and handle existing FK issues
IF @ScheduleID IN(SELECT schedule_id FROM msdb.dbo.sysjobschedules WHERE Job_ID=@JobID AND schedule_id=@ScheduleID)
AND @ScheduleID IN(SELECT schedule_id FROM msdb.dbo.sysschedules WHERE schedule_id=@ScheduleID)
BEGIN
PRINT 'This schedule_id exists in sysjobschedules'
PRINT 'This schedule_id exists in sysschedules'
END
ELSE
BEGIN
PRINT 'This schedule_id does not exist in sysjobschedules'
INSERT msdb.dbo.sysjobschedules
SELECT *
FROMmsdb_from_neptunium.dbo.sysjobschedules
WHEREjob_id = @JobID
AND schedule_id=@ScheduleID
AND schedule_id NOT IN(SELECT schedule_id FROM msdb_from_neptunium.dbo.sysschedules)
END
-- never returns any results
SELECT'Schedule Details'
SELECT*
FROMmsdb.dbo.sysjobschedules
WHERE1=1
AND schedule_id = @ScheduleID
AND job_id = @JobID
PRINT 'END'
-- debug
42D05898-EDC3-4E2B-B5E7-AFD1BA8D23D4
(1 row(s) affected)
(1 row(s) affected)
(100 row(s) affected)
DO Job Schedules
This Job does not exist in sysjobschedules
ScheduleID = 92
This schedule_id does not exist in sysjobschedules
(0 row(s) affected)
(1 row(s) affected)
(0 row(s) affected)
END
Any help would be appreciated!