SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
amit.x.mathur
amit.x.mathur
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 35
Comments posted to this topic are about the item Script all the jobs on a SQL server 2005 and apply them in SQL 2008
simon-970530
simon-970530
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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 ' '.
rene-500237
rene-500237
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2657 Visits: 1186
Hi,

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

Regards
vlad-548036
vlad-548036
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2830 Visits: 273
can't you just use SSMS to script the jobs ??
@DBA_ANDY
@DBA_ANDY
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 871
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!
jozwickdb-1026716
jozwickdb-1026716
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 198
Can someone confirm if they have tested in SQL 2005?
jozwickdb-1026716
jozwickdb-1026716
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 198
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 !!!
amit.x.mathur
amit.x.mathur
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 35
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.
jozwickdb-1026716
jozwickdb-1026716
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 198
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
amit.x.mathur
amit.x.mathur
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 35
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search