Recently, while upgrading a client from GP 10 to GP 2010 and SQL Server from 2005 to 2008 R2. SQL Server was going to be a clean install and not an in place upgrade and the client had over 200 SQL Jobs. Selecting all of the Jobs and using the Script Job as function failed. To get it to work, I had to break the list of Jobs into 30 separate lists using the Script Job as function. This would work for the Test Environment but not during the Production upgrade weekend. So off to Google I went in search for something that would automate the process. Here is what worked for me:
1. Create a new SQL Server Job on the 2005 server. I named my job ‘TEST’.
2. Change the job step to ActiveX Script
3. Paste in the following code in the Command area:
Dim conServer
Dim fso
Dim iFile
Dim oJB
Dim strJob
Dim strFilename
Const ioModeAppend = 8
Set conServer =
CreateObject(“SQLDMO.SQLServer”)
conServer.LoginSecure = True
conServer.Connect “.”
strFilename = “C:JOBS.sql”
strJob = “”
For Each oJB In
conServer.JobServer.Jobs
strJob = strJob &
“————————————————–” & vbCrLf
strJob = strJob & “–
SCRIPTING JOB: ” & oJB.Name & vbCrLf
strJob = strJob & “————————————————–”
& vbCrLf
strJob = strJob & oJB.Script()
& vbCrLf
Next
Set conServer = Nothing
Set fso =
CreateObject(“Scripting.FileSystemObject”)
Set iFile =
fso.CreateTextFile(strFilename, True)
iFile.Write (strJob)
iFile.Close
Set fso = Nothing
During Production upgrade weekend, execute the job. File will be saved to the C drive as
“C:JOBS.sql”. (you can change the following code ‘strFilename=”C:JOBS.sql”’ to save to a different location). Open the JOB.sql file in notepad and remove the TEST Job and if the SQL instance name has changed do a find and replace on the file with the new SQL instance name. Copy into Microsoft SQL Server Management Studio and execute script on the new SQL 2008 R2 Server. This VB script code works with SQL 2005.
