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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Amit Mathur » Script all the jobs on a SQL server 2005 and...

13 posts, Page 1 of 2

1 2 »»

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

Rate Topic

Display Mode

Topic Options

Author

Message

amit.x.mathur

Posted Wednesday, September 16, 2009 2:44 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 03, 2012 2:14 PM
Points: 2, Visits: 31

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

Post #789280

simon-970530

Posted Friday, September 18, 2009 2:01 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, September 18, 2009 2:14 PM
Points: 1, Visits: 1

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 ' '.

Post #790680

rene-500237

Posted Monday, September 21, 2009 12:16 AM

SSCrazy

Group: General Forum Members
Last Login: Friday, May 17, 2013 4:26 AM
Points: 2,300, Visits: 1,099

Hi,

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

Regards

Post #790951

vlad-548036

Posted Wednesday, September 23, 2009 11:52 AM

SSCommitted

Group: General Forum Members
Last Login: Monday, May 13, 2013 1:51 PM
Points: 1,825, Visits: 257

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

Post #792843

@DBA_ANDY

Posted Saturday, October 17, 2009 9:21 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, June 10, 2013 2:47 PM
Points: 18, Visits: 389

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!

Post #804693

jozwickdb-1026716

Posted Tuesday, January 26, 2010 1:15 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, December 16, 2011 7:33 AM
Points: 6, Visits: 154

Can someone confirm if they have tested in SQL 2005?

Post #853920

jozwickdb-1026716

Posted Thursday, January 28, 2010 7:01 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, December 16, 2011 7:33 AM
Points: 6, Visits: 154

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

Post #855183

amit.x.mathur

Posted Monday, February 01, 2010 8:34 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 03, 2012 2:14 PM
Points: 2, Visits: 31

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.

Post #857231

jozwickdb-1026716

Posted Monday, February 01, 2010 11:21 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, December 16, 2011 7:33 AM
Points: 6, Visits: 154

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

Post #857432

amit.x.mathur

Posted Tuesday, February 02, 2010 7:09 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 03, 2012 2:14 PM
Points: 2, Visits: 31

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

Post #857921

« Prev Topic | Next Topic »

13 posts, Page 1 of 2

1 2 »»

Permissions