Sacha Tomey

Sacha Tomey's Blog

Scripting SQL Server 2000 Jobs

SQL Server Agent Jobs can be scripted with the following SQL DMO VB Script:
 
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 "SQL2K_Server"

strFilename = "c:\SQLJobs.sql"

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

 
Loading