February 10, 2010 at 6:07 am
USE database_name
GO
DECLARE @JobID nvarchar(100),
@JobCategory varchar(128),
@JobCategoryClass varchar(128),@Now datetime,
@Nowtext varchar(30),@JobName nvarchar(120)
SET @now = GETDATE()
SELECT @Nowtext = CAST(@Now as varchar(30))
CREATE TABLE #Jobs (id int identity (1,1), jobid varchar(50))
INSERT INTO #Jobs (jobid) SELECT jobid = convert(varchar(50),job_id) FROM msdb.dbo.SysJobs WITH (NOLOCK)
DECLARE @MaxJobs int,@JobControl int
SELECT @JobControl = 1
SELECT @MaxJobs = MAX(id) FROM #jobs
--Create Jobs by looping through all the existing jobs on the server
WHILE (@JobControl <= @MaxJobs)
BEGIN --BEGIN Jobs
SELECT @JobID = JobID FROM #jobs WHERE id = @JobControl
SELECT @JobName = name FROM msdb.dbo.sysjobs_view WHERE Job_ID = @JobID
SELECT @JobCategory=sc.name,
@JobCategoryClass=category_class
FROM msdb.dbo.sysjobs sj
INNER JOIN msdb.dbo.syscategories sc ON sc.category_id=sj.category_id
WHERE Job_ID=@JobID
PRINT '/****** Object: Job ' + @JobName + ' Script Date:' + @Nowtext + ' ******/'
PRINT 'IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N''' + @JobName + ''')'
PRINT 'EXEC msdb.dbo.sp_delete_job @job_name= N''' + @JobName + ''''+ ', @delete_unused_schedule=1'
PRINT 'GO'
PRINT ''
PRINT '/****** Object: Job ' + @JobName + ' Script Date:' + @Nowtext + ' ******/'
PRINT 'BEGIN TRANSACTION'
PRINT 'DECLARE @ReturnCode INT'
PRINT 'SELECT @ReturnCode = 0'
PRINT '/****** Object: JobCategory ' + QUOTENAME(@JobCategory) + ' Script Date:' + @Nowtext + ' ******/'
PRINT 'IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name = N''' + @JobCategory + ''' AND category_class = ' + @JobCategoryClass+ ')'
PRINT 'BEGIN'
PRINT 'EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N''JOB'', @type=N''LOCAL'', @name = N''' + @JobCategory + ''''
PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'
PRINT ''
PRINT 'END'
PRINT ''
PRINT 'DECLARE @jobId BINARY(16)'
PRINT ''
DECLARE @enabled int,
@notify_level_eventlog int,
@notify_level_email int,
@notify_level_netsend int,
@notify_level_page int,
@delete_level int,
@description nvarchar(128),
@category_name nvarchar(128),
@owner_login_name nvarchar(128),
@notify_email_operator_name nvarchar(128)
SELECT @enabled = sj.enabled,
@notify_level_eventlog = sj.notify_level_eventlog,
@notify_level_email = sj.notify_level_email,
@notify_level_netsend = sj.notify_level_netsend,
@notify_level_page = sj.notify_level_page,
@delete_level = sj.delete_level,
@description = sj.[description],
@category_name = sc.name,
@owner_login_name = SUSER_NAME(sj.owner_sid),
@notify_email_operator_name = so.name
FROM msdb.dbo.sysjobs sj
INNER JOIN msdb.dbo.syscategories sc
on sc.category_id = sj.category_id
LEFT OUTER JOIN msdb.dbo.sysoperators so
ON sj.notify_email_operator_id = so.id
WHERE Job_ID = @JobID
PRINT 'EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name = N''' + @JobName + ''','
PRINT ' @enabled=' + CAST(@enabled as varchar(30))+ ','
PRINT ' @notify_level_eventlog=' + CAST(@notify_level_eventlog as varchar(30))+ ','
PRINT ' @notify_level_email=' + CAST(@notify_level_email as varchar(30))+ ','
PRINT ' @notify_level_netsend=' + CAST(@notify_level_netsend as varchar(30))+ ','
PRINT ' @notify_level_page=' + CAST(@notify_level_page as varchar(30))+ ','
PRINT ' @delete_level=' + CAST(@delete_level as varchar(30))+ ','
PRINT ' @description=N''' + REPLACE(@description, '''','''''') + ''','
PRINT ' @category_name=N''' + @category_name + ''','
PRINT ' @owner_login_name=N''' + ISNULL(@owner_login_name,'sa') + ''','
IF @notify_email_operator_name IS NOT NULL
BEGIN
PRINT ' @notify_email_operator_name=N''' + @notify_email_operator_name + ''', @job_id = @JobID OUTPUT'
END
ELSE
BEGIN
PRINT ' @job_id = @JobID OUTPUT'
END
PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'
PRINT ''
--CREATE STEPS
DECLARE @MaxSteps int,
@LoopControl int
SELECT @LoopControl = 1
SELECT @MaxSteps = MAX(step_id) FROM msdb.dbo.sysjobsteps WHERE Job_ID = @JobID
WHILE (@LoopControl <= @MaxSteps)
BEGIN
DECLARE @step_name nvarchar (128), @step_id int,
@cmdexec_success_code int,
@on_success_action int,
@on_success_step_id int,
@on_fail_action int,
@on_fail_step_id int,
@retry_attempts int,
@retry_interval int,
@os_run_priority int,
@subsystem nvarchar (128),
@command nvarchar (max),
@database_name nvarchar(128),
@flags int
SELECT @step_name = step_name,
@step_id = step_id,
@cmdexec_success_code = cmdexec_success_code,
@on_success_action = on_success_action,
@on_success_step_id = on_success_step_id,
@on_fail_action = on_fail_action,
@on_fail_step_id = on_fail_step_id,
@retry_attempts = retry_attempts,
@retry_interval = retry_interval,
@os_run_priority = os_run_priority,
@subsystem = subsystem,
@command = command,
@database_name = database_name,
@flags = flags
FROM msdb.dbo.sysjobsteps WHERE Job_ID = @JobID
AND step_id = @LoopControl
PRINT ''
PRINT '/****** Object: Step ' + @step_name + ' Script Date: ' + @Nowtext + '******/'
PRINT 'EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N''' + @step_name + ''','
PRINT ' @step_id=' + CAST(@step_id as varchar(30))+ ','
PRINT ' @cmdexec_success_code=' + CAST(@cmdexec_success_code as varchar(30))+ ','
PRINT ' @on_success_action=' + CAST(@on_success_action as varchar(30))+ ','
PRINT ' @on_success_step_id=' + CAST(@on_success_step_id as varchar(30))+ ','
PRINT ' @on_fail_action=' + CAST(@on_fail_action as varchar(30))+ ','
PRINT ' @on_fail_step_id=' + CAST(@on_fail_step_id as varchar(30))+ ','
PRINT ' @retry_attempts=' + CAST(@retry_attempts as varchar(30))+ ','
PRINT ' @retry_interval=' + CAST(@retry_interval as varchar(30))+ ','
PRINT ' @os_run_priority=' + CAST(@os_run_priority as varchar(30))+ ', @subsystem=N''' + @subsystem + ''','
PRINT ' @command=N''' + REPLACE(@command, '''','''''') + ''','
PRINT ' @database_name=N''' + @database_name + ''','
PRINT ' @flags=' + CAST(@flags as varchar(30))
PRINT ''
SELECT @LoopControl = @LoopControl + 1
END -- End Steps While
PRINT ''
PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'
PRINT 'EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1'
PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'
PRINT ''
--CREATE SCHEDULES
DECLARE @MaxSchedules int,
@SchedulesLoopControl int
SELECT @SchedulesLoopControl = 1
CREATE TABLE #Schedules (id int identity (1,1), schedule_id int)
INSERT INTO #Schedules (schedule_id) SELECT schedule_id = sjs.schedule_id
FROM msdb.dbo.sysjobschedules sjs WITH (NOLOCK)
--INNER JOIN msdb.dbo.sysschedules ss WITH (NOLOCK) ON sjs.schedule_id = ss.schedule_id
WHERE sjs.Job_ID = @JobID
SELECT @MaxSchedules = MAX(id) FROM #Schedules
IF EXISTS (SELECT COUNT(*) FROM #Schedules)
BEGIN
WHILE (@SchedulesLoopControl <= @MaxSchedules)
BEGIN
DECLARE @name nvarchar(2000),
@sch_enabled int,
@freq_type int,
@freq_interval int,
@freq_subday_type int,
@freq_subday_interval int,
@freq_relative_interval int,
@freq_recurrence_factor int,
@active_start_date int,
@active_end_date int,
@active_start_time int,
@active_end_time int,
@schedule_uid nvarchar (50)
SELECT @name = name,
@sch_enabled = enabled,
@freq_type = freq_type,
@freq_interval = freq_interval,
@freq_subday_type = freq_subday_type,
@freq_subday_interval = freq_subday_interval,
@freq_relative_interval = freq_relative_interval,
@freq_recurrence_factor = freq_recurrence_factor,
@active_start_date = active_start_date,
@active_end_date = active_end_date,
@active_start_time = active_start_time,
@active_end_time = active_end_time,
@schedule_uid = schedule_uid
FROM msdb.dbo.sysjobschedules sjs WITH (NOLOCK)
INNER JOIN msdb.dbo.sysschedules ss WITH (NOLOCK) ON sjs.schedule_id = ss.schedule_id
INNER JOIN #Schedules s ON ss.schedule_id = s.schedule_id
WHERE sjs.Job_ID = @JobID
AND s.id = @SchedulesLoopControl
PRINT 'EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N''' + REPLACE(@name, '''','''''') + ''','
PRINT ' @enabled=' + CAST(@sch_enabled as varchar(30))+ ','
PRINT ' @freq_type=' + CAST(@freq_type as varchar(30))+ ','
PRINT ' @freq_interval=' + CAST(@freq_interval as varchar(30))+ ','
PRINT ' @freq_subday_type=' + CAST(@freq_subday_type as varchar(30))+ ','
PRINT ' @freq_subday_interval=' + CAST(@freq_subday_interval as varchar(30))+ ','
PRINT ' @freq_relative_interval=' + CAST(@freq_relative_interval as varchar(30))+ ','
PRINT ' @freq_recurrence_factor=' + CAST(@freq_recurrence_factor as varchar(30))+ ','
PRINT ' @active_start_date=' + CAST(@active_start_date as varchar(30))+ ','
PRINT ' @active_end_date=' + CAST(@active_end_date as varchar(30))+ ','
PRINT ' @active_start_time=' + CAST(@active_start_time as varchar (30)) + ','
PRINT ' @active_end_time=' + CAST(@active_end_time as varchar (30)) + ','
PRINT ' @schedule_uid=N''' + @schedule_uid + ''''
PRINT ''
PRINT ''
PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'
PRINT ''
SELECT @SchedulesLoopControl = @SchedulesLoopControl + 1
END -- End Schedules While loop
END -- END IF (SELECT COUNT(*) FROM #Schedules) > 0
DECLARE @server_name varchar(30)
SELECT @server_name = CASE server_id WHEN 0 THEN 'local' ELSE 'Multi-Server' END
FROM msdb.dbo.sysjobservers WHERE Job_ID = @JobID
PRINT 'EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name =N''(' + @server_name + ')'''
PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'
PRINT 'COMMIT TRANSACTION'
PRINT 'GOTO EndSave'
PRINT 'QuitWithRollback:'
PRINT ' IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION'
PRINT 'EndSave:'
PRINT ''
PRINT 'GO'
PRINT ''
PRINT ''
SELECT @JobControl = @JobControl + 1
DROP TABLE #Schedules
END --End Jobs
DROP TABLE #Jobs
can anybody tell me ?
😀
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
February 10, 2010 at 6:11 am
What's the error?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 10, 2010 at 6:19 am
Msg 102, Level 15, State 1, Line 21
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 68
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 73
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 74
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 75
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 76
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 77
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 78
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 79
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 80
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 83
Incorrect syntax near '?'.
Msg 156, Level 15, State 1, Line 85
Incorrect syntax near the keyword 'ELSE'.
Msg 102, Level 15, State 1, Line 93
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 95
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 97
Incorrect syntax near '?'.
Msg 137, Level 15, State 2, Line 97
Must declare the scalar variable "@LoopControl".
Msg 102, Level 15, State 1, Line 99
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 132
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 133
Incorrect syntax near '?'.
Msg 137, Level 15, State 2, Line 133
Must declare the scalar variable "@step_id".
Msg 137, Level 15, State 2, Line 134
Must declare the scalar variable "@cmdexec_success_code".
Msg 137, Level 15, State 2, Line 135
Must declare the scalar variable "@on_success_action".
Msg 137, Level 15, State 2, Line 136
Must declare the scalar variable "@on_success_step_id".
Msg 137, Level 15, State 2, Line 137
Must declare the scalar variable "@on_fail_action".
Msg 137, Level 15, State 2, Line 138
Must declare the scalar variable "@on_fail_step_id".
Msg 137, Level 15, State 2, Line 139
Must declare the scalar variable "@retry_attempts".
Msg 137, Level 15, State 2, Line 140
Must declare the scalar variable "@retry_interval".
Msg 137, Level 15, State 2, Line 141
Must declare the scalar variable "@os_run_priority".
Msg 137, Level 15, State 2, Line 142
Must declare the scalar variable "@command".
Msg 137, Level 15, State 2, Line 143
Must declare the scalar variable "@database_name".
Msg 137, Level 15, State 2, Line 144
Must declare the scalar variable "@flags".
Msg 137, Level 15, State 2, Line 146
Must declare the scalar variable "@LoopControl".
Msg 102, Level 15, State 1, Line 158
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 161
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 163
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 164
Incorrect syntax near '?'.
Msg 319, Level 15, State 1, Line 164
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Msg 102, Level 15, State 1, Line 168
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 171
Incorrect syntax near '?'.
Msg 137, Level 15, State 2, Line 172
Must declare the scalar variable "@SchedulesLoopControl".
Msg 102, Level 15, State 1, Line 175
Incorrect syntax near '?'.
Msg 319, Level 15, State 1, Line 201
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Msg 319, Level 15, State 1, Line 202
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Msg 102, Level 15, State 1, Line 208
Incorrect syntax near '?'.
Msg 137, Level 15, State 2, Line 208
Must declare the scalar variable "@sch_enabled".
Msg 137, Level 15, State 2, Line 209
Must declare the scalar variable "@freq_type".
Msg 137, Level 15, State 2, Line 210
Must declare the scalar variable "@freq_interval".
Msg 137, Level 15, State 2, Line 211
Must declare the scalar variable "@freq_subday_type".
Msg 137, Level 15, State 2, Line 212
Must declare the scalar variable "@freq_subday_interval".
Msg 137, Level 15, State 2, Line 213
Must declare the scalar variable "@freq_relative_interval".
Msg 137, Level 15, State 2, Line 214
Must declare the scalar variable "@freq_recurrence_factor".
Msg 137, Level 15, State 2, Line 215
Must declare the scalar variable "@active_start_date".
Msg 137, Level 15, State 2, Line 216
Must declare the scalar variable "@active_end_date".
Msg 137, Level 15, State 2, Line 217
Must declare the scalar variable "@active_start_time".
Msg 137, Level 15, State 2, Line 218
Must declare the scalar variable "@active_end_time".
Msg 137, Level 15, State 2, Line 219
Must declare the scalar variable "@schedule_uid".
Msg 137, Level 15, State 2, Line 226
Must declare the scalar variable "@SchedulesLoopControl".
Msg 102, Level 15, State 1, Line 231
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 232
Incorrect syntax near '?'.
Msg 137, Level 15, State 2, Line 233
Must declare the scalar variable "@server_name".
Msg 102, Level 15, State 1, Line 247
Incorrect syntax near '?'.
Msg 156, Level 15, State 1, Line 251
Incorrect syntax near the keyword 'DROP'.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
February 10, 2010 at 6:38 am
Invalid "non printable" characters in your script .....
Copy / paste it into e.g. Word and replace the found non-printable characters by a space.
e.g. SQL2000 EM allowed char(13) to be used ..... SSMS nolonger allows this. ( use char(10) )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 10, 2010 at 6:40 am
Good catch , i also recovered the issue. but did u get any sign that this script contains
junk character before copying it to MS-word/notepad.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
February 10, 2010 at 6:48 am
experience .... ehum gut feeling :w00t:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply