Post reply

How to script a Job...

  • February 7, 2005 at 8:33 am

    #109174

    Hi,

    I have about 100 jobs and want to know if there is a way to script all jobs but as sperate files. I know you can right click on Jobs in EM, and generate sql, but this scripts them all in one large file.

    Any help appreciated.

     

    Jeet


    Thanks Jeet

  • February 7, 2005 at 3:52 pm

    #541041

    Try this. Fill in the blanks as indicated by << >>. Put it in a job with a ActiveX Script step.

    One thing you might want to check is where the filename is generated. If you have any weird characters it might generate an invalid filename.

    Dim conSrvr, Srvr, oJB, strJob
Dim fso, iFile, strFldr, strFilename
Dim dteNow, strDate
    strServer = "<< Your Server name >>"
    dteNow = Now()
strDate = CStr(YEAR(dteNow))
strDate = strDate & RIGHT("00" & MONTH(dteNow),2)
strDate = strDate & RIGHT("00" & DAY(dteNow),2)
strDate = strDate & RIGHT("00" & HOUR(dteNow),2)
strDate = strDate & RIGHT("00" & MINUTE(dteNow),2)
strDate = strDate & RIGHT("00" & SECOND(dteNow),2)
    strFldr = "<< Folder to store file(s) >>" & strServer & "\"
    Set fso = CreateObject("Scripting.FileSystemObject")
Set conServer = CreateObject("SQLDMO.SQLServer")
conServer.LoginSecure = True
conServer.Connect strServer
    For Each oJB In conServer.JobServer.Jobs
 strFilename = strFldr & oJB.Name & ".sql" 
     Set iFile = fso.CreateTextFile(strFilename, True)
  
 strJob = strJob & "--------------------------------------------------" & vbCrLf
 strJob = strJob & "-- SCRIPTING JOB: " & oJB.Name & vbCrLf
 strJob = strJob & "--------------------------------------------------" & vbCrLf
 strJob = strJob & oJB.Script() & vbCrLf
     iFile.Write (strJob) 
 iFile.Close
    Next
    Set conServer = Nothing
Set fso = Nothing

     

    --------------------
    Colt 45 - the original point and click interface

  • February 8, 2005 at 3:27 am

    #541085

    Many thanks for that. This technique works a treat!!


    Thanks Jeet

  • February 8, 2005 at 3:03 pm

    #541277

    Something strange. It does indeed create a new file for all jobs I got. But it also adds another another jobs to the same file. What could be the case?

  • February 8, 2005 at 3:27 pm

    #541282

    Most likely culprit is that the strJob variable isn't re-initialised. It should work better if you change this line after the CreateTextFile line,

     strJob = strJob & "--------------------------------------------------" & vbCrLf

    to this

     strJob = "--------------------------------------------------" & vbCrLf

    The script I posted was hacked from one that generated a single file for disabled jobs. When I started at this job they hadn't thought about deleting job they didn't use anymore, they just disabled them. There were over 200 jobs on one server and only about 20 of them were enabled.

     

    --------------------
    Colt 45 - the original point and click interface

  • February 9, 2005 at 2:28 am

    #541322

    There are indeed some errors in the script, however It was the technique that I found invaluable(..and much appreciated). I modified it slightly to be :

    Option Explicit

    Dim conServer, oJB

    Dim strFldr, strFilename, strServer

    'Set server and output folder

    strServer = "<Server name>"

    strFldr = "<Output folder>"

    'Create object and connect

    Set conServer = CreateObject("SQLDMO.SQLServer")

    conServer.LoginSecure = True

    conServer.Connect strServer

    'loop around each job and script it

    For Each oJB In conServer.JobServer.Jobs

     'set filename

     strFilename = strFldr & oJB.Name & " Job.sql"

     

     'script it replacing old jobs, with headers & ansii format

     oJB.Script 131077, strFilename, 2

    Next

    Set conServer = Nothing

     

     


    Thanks Jeet

  • February 9, 2005 at 3:56 pm

    #541484

    It works, Jeet. Thanks.

  • May 24, 2006 at 8:56 am

    #639811

    Thanks from me aswell.

    Phil.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply