Script all the jobs on a SQL server 2005 and apply them in SQL 2008

  • Comments posted to this topic are about the item Script all the jobs on a SQL server 2005 and apply them in SQL 2008

  • I've copied and pasted your script into a 2005 query window, and tried to run it, but all I get are errors where there just shouldn't be any...

    Is there something obvious I'm doing wrong ? I tried changing the db to msdb, master and a production one, but that made no difference.

    Thanks

    Simon

    Msg 102, Level 15, State 1, Line 13

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 20

    Incorrect syntax near '?'.

    Msg 137, Level 15, State 2, Line 21

    Must declare the scalar variable "@Now".

    Msg 102, Level 15, State 1, Line 29

    Incorrect syntax near '?'.

    Msg 137, Level 15, State 1, Line 30

    Must declare the scalar variable "@JobControl".

    Msg 137, Level 15, State 2, Line 34

    Must declare the scalar variable "@JobControl".

    Msg 137, Level 15, State 2, Line 37

    Must declare the scalar variable "@JobControl".

    Msg 137, Level 15, State 1, Line 39

    Must declare the scalar variable "@JobName".

    Msg 102, Level 15, State 1, Line 40

    Incorrect syntax near '?'.

    Msg 137, Level 15, State 2, Line 46

    Must declare the scalar variable "@JobName".

    Msg 137, Level 15, State 2, Line 47

    Must declare the scalar variable "@JobName".

    Msg 137, Level 15, State 2, Line 48

    Must declare the scalar variable "@JobName".

    Msg 137, Level 15, State 2, Line 51

    Must declare the scalar variable "@JobName".

    Msg 137, Level 15, State 2, Line 55

    Must declare the scalar variable "@JobCategory".

    Msg 137, Level 15, State 2, Line 56

    Must declare the scalar variable "@JobCategory".

    Msg 137, Level 15, State 2, Line 58

    Must declare the scalar variable "@JobCategory".

    Msg 102, Level 15, State 1, Line 66

    Incorrect syntax near '?'.

    Msg 137, Level 15, State 2, Line 93

    Must declare the scalar variable "@JobName".

    Msg 102, Level 15, State 1, Line 95

    Incorrect syntax near '?'.

    Msg 137, Level 15, State 2, Line 95

    Must declare the scalar variable "@notify_level_eventlog".

    Msg 137, Level 15, State 2, Line 96

    Must declare the scalar variable "@notify_level_email".

    Msg 137, Level 15, State 2, Line 97

    Must declare the scalar variable "@notify_level_netsend".

    Msg 137, Level 15, State 2, Line 98

    Must declare the scalar variable "@notify_level_page".

    Msg 137, Level 15, State 2, Line 99

    Must declare the scalar variable "@delete_level".

    Msg 137, Level 15, State 2, Line 100

    Must declare the scalar variable "@description".

    Msg 137, Level 15, State 2, Line 101

    Must declare the scalar variable "@category_name".

    Msg 137, Level 15, State 2, Line 102

    Must declare the scalar variable "@owner_login_name".

    Msg 137, Level 15, State 2, Line 103

    Must declare the scalar variable "@notify_email_operator_name".

    Msg 137, Level 15, State 2, Line 105

    Must declare the scalar variable "@notify_email_operator_name".

    Msg 102, Level 15, State 1, Line 115

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 117

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 119

    Incorrect syntax near '?'.

    Msg 137, Level 15, State 2, Line 119

    Must declare the scalar variable "@LoopControl".

    Msg 102, Level 15, State 1, Line 122

    Incorrect syntax near '?'.

    Msg 137, Level 15, State 2, Line 153

    Must declare the scalar variable "@Nowtext".

    Msg 102, Level 15, State 1, Line 156

    Incorrect syntax near '?'.

    Msg 137, Level 15, State 2, Line 156

    Must declare the scalar variable "@step_id".

    Msg 137, Level 15, State 2, Line 157

    Must declare the scalar variable "@cmdexec_success_code".

    Msg 137, Level 15, State 2, Line 158

    Must declare the scalar variable "@on_success_action".

    Msg 137, Level 15, State 2, Line 159

    Must declare the scalar variable "@on_success_step_id".

    Msg 137, Level 15, State 2, Line 160

    Must declare the scalar variable "@on_fail_action".

    Msg 137, Level 15, State 2, Line 161

    Must declare the scalar variable "@on_fail_step_id".

    Msg 137, Level 15, State 2, Line 162

    Must declare the scalar variable "@retry_attempts".

    Msg 137, Level 15, State 2, Line 163

    Must declare the scalar variable "@retry_interval".

    Msg 137, Level 15, State 2, Line 164

    Must declare the scalar variable "@os_run_priority".

    Msg 137, Level 15, State 2, Line 165

    Must declare the scalar variable "@command".

    Msg 137, Level 15, State 2, Line 166

    Must declare the scalar variable "@database_name".

    Msg 137, Level 15, State 2, Line 167

    Must declare the scalar variable "@flags".

    Msg 137, Level 15, State 2, Line 169

    Must declare the scalar variable "@LoopControl".

    Msg 102, Level 15, State 1, Line 181

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 184

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 186

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 187

    Incorrect syntax near '?'.

    Msg 319, Level 15, State 1, Line 187

    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 191

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 194

    Incorrect syntax near '?'.

    Msg 137, Level 15, State 2, Line 195

    Must declare the scalar variable "@SchedulesLoopControl".

    Msg 102, Level 15, State 1, Line 198

    Incorrect syntax near '?'.

    Msg 319, Level 15, State 1, Line 224

    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 225

    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 231

    Incorrect syntax near '?'.

    Msg 137, Level 15, State 2, Line 231

    Must declare the scalar variable "@sch_enabled".

    Msg 137, Level 15, State 2, Line 232

    Must declare the scalar variable "@freq_type".

    Msg 137, Level 15, State 2, Line 233

    Must declare the scalar variable "@freq_interval".

    Msg 137, Level 15, State 2, Line 234

    Must declare the scalar variable "@freq_subday_type".

    Msg 137, Level 15, State 2, Line 235

    Must declare the scalar variable "@freq_subday_interval".

    Msg 137, Level 15, State 2, Line 236

    Must declare the scalar variable "@freq_relative_interval".

    Msg 137, Level 15, State 2, Line 237

    Must declare the scalar variable "@freq_recurrence_factor".

    Msg 137, Level 15, State 2, Line 238

    Must declare the scalar variable "@active_start_date".

    Msg 137, Level 15, State 2, Line 239

    Must declare the scalar variable "@active_end_date".

    Msg 137, Level 15, State 2, Line 240

    Must declare the scalar variable "@active_start_time".

    Msg 137, Level 15, State 2, Line 241

    Must declare the scalar variable "@active_end_time".

    Msg 137, Level 15, State 2, Line 242

    Must declare the scalar variable "@schedule_uid".

    Msg 137, Level 15, State 2, Line 249

    Must declare the scalar variable "@SchedulesLoopControl".

    Msg 102, Level 15, State 1, Line 254

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 255

    Incorrect syntax near '?'.

    Msg 137, Level 15, State 2, Line 256

    Must declare the scalar variable "@server_name".

    Msg 137, Level 15, State 2, Line 268

    Must declare the scalar variable "@JobControl".

    Msg 156, Level 15, State 1, Line 274

    Incorrect syntax near the keyword 'DROP'.

    Msg 102, Level 15, State 1, Line 279

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 280

    Incorrect syntax near '?'.

  • Hi,

    copy the script to Notepad and clean there all spec chars. Run then this script. It'll work then.

    Regards

  • can't you just use SSMS to script the jobs ??

  • We have 120 servers and unfortunately we have groups with way too much access to modify things that we politically can't do anything about.

    We have been looking for a way to automate this process rather than connecting to 120 servers one-by-one in SSMS every week to take a DR script of the jobs...this appears to work (in 2005 anyway - looks like it needs some simle mod's to work on 2000 (sigh))

    Thanks!

  • Can someone confirm if they have tested in SQL 2005?

  • I tried running this script on SQL2005, then using the output to recreate scripts on SQL2005. I then compared your output with the output generated by running the "generate script" in SSMC. A few differences arise. Can you tell me if it is due to running output in SQL2005 rather than on SQL2008? Perhaps the differences address "bugs/improvements" that are corrected in SQL2008?

    #1 Error checking line is not included after each step in a job.

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    #2 In call to sp_add_jobschedule, your script passes the schedule_uid, where the SSMC does not.

    Can anyone comment ? Amit?

    Excellent tool - thank you !!!

  • Hi jozwickdb-1026716,

    #1 - Ideally the script should generate Error check after every step. There must be some changes required that are cosmetic to some jobs in your environment. Can you have a look after which step it is not generating error check? May be you could see something missing there?

    #2 - this is intentional and was suppose to script out from 2005 and create them in 2008, the parameter schedule_id is changed in 2008 to be schedule_uid. If you need to run it back in 2005 again just do a simple change in original script where schedules are created. Change the parameter name from schedule_uid to schedule_id and the o/p script will work good in 2005 too.

    Hope this answers your question.

  • Hello!

    Thank you for the prompt reply. Regarding #1 in my post, your script generates the following. This particular job has 54 steps. The error checking only shows up before step 1 (see below first code excerpt) and after the last step (#54). I also included code generated by SSMC. I will wait for your comment. I need to look at #2 that you addressed. Thank you.

    $$$$$$$$$$ SCript excerpt generated from your code:$$$$$$

    /****** Object: Job (03:30 - Sa) ReBuild Chamaeleon and ThePrecious Script Date:Jan 28 2010 1:26PM ******/

    BEGIN TRANSACTION

    DECLARE @ReturnCode INT

    SELECT @ReturnCode = 0

    /****** Object: JobCategory [[Uncategorized (Local)]]] Script Date:Jan 28 2010 1:26PM ******/

    IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]' AND category_class = 1)

    BEGIN

    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name = N'[Uncategorized (Local)]'

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    END

    DECLARE @jobId BINARY(16)

    EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name = N'(03:30 - Sa) ReBuild Chamaeleon and ThePrecious',

    @enabled=1,

    @notify_level_eventlog=2,

    @notify_level_email=3,

    @notify_level_netsend=0,

    @notify_level_page=0,

    @delete_level=0,

    @description=N'No description available.',

    @category_name=N'[Uncategorized (Local)]',

    @owner_login_name=N'sa',

    @notify_email_operator_name=N'CCBHDBA', @job_id = @JobID OUTPUT

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    /****** Object: Step Truncate Cham Tables Script Date: Jan 28 2010 1:26PM******/

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Truncate Cham Tables',

    @step_id=1,

    @cmdexec_success_code=0,

    @on_success_action=3,

    @on_success_step_id=0,

    @on_fail_action=2,

    @on_fail_step_id=0,

    @retry_attempts=0,

    @retry_interval=1,

    @os_run_priority=0, @subsystem=N'TSQL',

    @command=N'EXEC asp_truncate_tables ''N''

    ',

    @database_name=N'Chamaeleon',

    @flags=0

    /****** Object: Step Disconnect CCBHProd Users Script Date: Jan 28 2010 1:26PM******/

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Disconnect CCBHProd Users',

    @step_id=2,

    @cmdexec_success_code=0,

    @on_success_action=3,

    @on_success_step_id=0,

    @on_fail_action=2,

    @on_fail_step_id=0,

    @retry_attempts=0,

    @retry_interval=1,

    @os_run_priority=0, @subsystem=N'TSQL',

    @command=N'exec asp_kill_connects ''CCBHProd''',

    @database_name=N'master',

    @flags=0

    /****** Object: Step Restore CCBHProd Script Date: Jan 28 2010 1:26PM******/

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Restore CCBHProd',

    @step_id=3,

    @cmdexec_success_code=0,

    $$$$$$$ Script generated from SSMC: $$$$$$$$

    /****** Object: Job [(03:30 - Sa) ReBuild Chamaeleon and ThePrecious] Script Date: 01/28/2010 11:38:51 ******/

    BEGIN TRANSACTION

    DECLARE @ReturnCode INT

    SELECT @ReturnCode = 0

    /****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 01/28/2010 11:38:51 ******/

    IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

    BEGIN

    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    END

    DECLARE @jobId BINARY(16)

    EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'(03:30 - Sa) ReBuild Chamaeleon and ThePrecious',

    @enabled=1,

    @notify_level_eventlog=2,

    @notify_level_email=3,

    @notify_level_netsend=0,

    @notify_level_page=0,

    @delete_level=0,

    @description=N'No description available.',

    @category_name=N'[Uncategorized (Local)]',

    @owner_login_name=N'sa',

    @notify_email_operator_name=N'CCBHDBA', @job_id = @jobId OUTPUT

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    /****** Object: Step [Truncate Cham Tables] Script Date: 01/28/2010 11:38:52 ******/

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Truncate Cham Tables',

    @step_id=1,

    @cmdexec_success_code=0,

    @on_success_action=3,

    @on_success_step_id=0,

    @on_fail_action=2,

    @on_fail_step_id=0,

    @retry_attempts=0,

    @retry_interval=1,

    @os_run_priority=0, @subsystem=N'TSQL',

    @command=N'EXEC asp_truncate_tables ''N''

    ',

    @database_name=N'Chamaeleon',

    @flags=0

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    /****** Object: Step [Disconnect CCBHProd Users] Script Date: 01/28/2010 11:38:52 ******/

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Disconnect CCBHProd Users',

    @step_id=2,

    @cmdexec_success_code=0,

    @on_success_action=3,

    @on_success_step_id=0,

    @on_fail_action=2,

    @on_fail_step_id=0,

    @retry_attempts=0,

    @retry_interval=1,

    @os_run_priority=0, @subsystem=N'TSQL',

    @command=N'exec asp_kill_connects ''CCBHProd''',

    @database_name=N'master',

    @flags=0

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  • I see what you mean - for that there is a small change required in the script, need to bring up the error generating statement in Loop where Steps are created. I am new to this website, not sure how to edit original script that I posted. If you know that, please let me know so that I can update the original script.

    Else -

    In the original script

    bring Line #172 : ???PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'

    Before Line #169: ????????????SELECT @LoopControl = @LoopControl + 1

    i.e. the code should be like:

    ----------------------Code Snippet To Be-----------------------------

    ????????????PRINT '????????@database_name=N''' + @database_name + ''','

    ????????????PRINT '????????@flags=' + CAST(@flags as varchar(30))

    ????????????PRINT ''

    ????PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'

    ????????????SELECT @LoopControl = @LoopControl + 1

    ????????END -- End Steps While

    ????PRINT ''

    ????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

  • hi Amit!

    Thank you again for your assistance.

    According to your suggestion, I moved line 172 before line 169 and that seems to have resolved the discrepancy for the error checking.

    On #2 in my post, I did a search on "schedule_uid" in the original script and replaced with "schedule_id" to change the field so that jobs can be recreated on SQL2005 instead of SQL2008. I ran your script on a test server.

    It generated the code to script out all jobs. However, when I went to run it, I received the following:

    Msg 209, Level 16, State 1, Line 211

    Ambiguous column name 'schedule_id'.

    Not sure if I am not understanding something in the logic.

    And, no, as I am new to this post, I do not know how to update a submitted script. I did send an email to sqlservercentral and asked. I hope they reply.

    Let me know what you think about my schedule_id issue.

    Thank you !!

  • Amit,

    I heard back from the folks at SQLServerCentral and you are to do the following to edit your script:

    If you go into the contribution center and edit it, it will get resubmitted. http://www.sqlservercentral.com/Contributions/Home

    Hope this helps.

    Looking forward to your response to my #2 question above.

  • A point of note.

    If you need to use this script to recover the jobs from a restored copy of msdb that isn't named "msdb", you'll need to recreate/alter the system views dbo.sysjobs_view, and dbo.sysoriginatingservers_view to reflect the restored DB name.

    rj

  • Thanks so much for this script - it's fantastic!

    The only issue I ran into is that some of our job names and step names have single quotes in them, which throws off the quoting in the script and causes some issues. To resolve this, I had to make the following change:

    Change "@step_name" on line 147 to "REPLACE(@step_name, '''', '''''')"

    Otherwise amazing - this script saved hours of work!

    Ryan

  • Thanks for the script.

Viewing 15 posts - 1 through 14 (of 14 total)

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