September 16, 2009 at 2:44 pm
Comments posted to this topic are about the item Script all the jobs on a SQL server 2005 and apply them in SQL 2008
September 18, 2009 at 2:01 pm
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 '?'.
September 21, 2009 at 12:16 am
Hi,
copy the script to Notepad and clean there all spec chars. Run then this script. It'll work then.
Regards
September 23, 2009 at 11:52 am
can't you just use SSMS to script the jobs ??
October 17, 2009 at 9:21 am
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!
January 26, 2010 at 1:15 pm
Can someone confirm if they have tested in SQL 2005?
January 28, 2010 at 7:01 am
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 !!!
February 1, 2010 at 8:34 am
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.
February 1, 2010 at 11:21 am
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
February 2, 2010 at 7:09 am
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
February 2, 2010 at 9:05 am
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 !!
February 2, 2010 at 9:20 am
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.
May 3, 2011 at 7:39 pm
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
May 26, 2015 at 10:02 am
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
May 24, 2016 at 6:48 am
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