How to automatically generate SQL script for all jobs. (SQL7)

[TheJFM]

As part of routine maintenance I script all jobs through Enterprise manager.

Is there a way of calling the procedure that scripts all jobs in order to make this a regular task?

** Don't reach for the moon when you have the stars. ;-) TheJFM **

 

[JayKusch]

1. Create a job; ActiveX Script.

2. Paste the code; change the server/path to your desired values.



Dim conServer
Dim fso
Dim iFile
Dim oJB
Dim strJob
Dim strFilename
Const ioModeAppend = 8

Set conServer = CreateObject("SQLDMO.SQLServer&quot
conServer.LoginSecure = True
conServer.Connect &quot;<servername>&quot;

strFilename = &quot;<path where output file will be placed>_JOBS.sql&quot;

For Each oJB In conServer.JobServer.Jobs
strJob = strJob & &quot;--------------------------------------------------&quot; & vbCrLf
strJob = strJob & &quot;-- SCRIPTING JOB: &quot; & oJB.Name & vbCrLf
strJob = strJob & &quot;--------------------------------------------------&quot; & vbCrLf
strJob = strJob & oJB.Script() & vbCrLf
Next
Set conServer = Nothing

Set fso = CreateObject(&quot;Scripting.FileSystemObject&quot
Set iFile = fso.CreateTextFile(strFilename, True)
iFile.Write (strJob)
iFile.Close
Set fso = Nothing




Thanks

J. Kusch