Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Script all the jobs on a SQL server 2005 and apply them in SQL 2008 Expand / Collapse
Author
Message
Posted Tuesday, February 2, 2010 9:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 25, 2015 8:37 AM
Points: 6, Visits: 167
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 !!
Post #858039
Posted Tuesday, February 2, 2010 9:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 25, 2015 8:37 AM
Points: 6, Visits: 167
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.
Post #858050
Posted Tuesday, May 3, 2011 7:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 26, 2015 11:08 AM
Points: 2, Visits: 246
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
Post #1102814
Posted Tuesday, May 26, 2015 10:02 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, June 25, 2015 3:53 PM
Points: 183, Visits: 155
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
Post #1688780
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse