Post reply

SQL Jobs script

  • February 19, 2009 at 11:51 am

    #201323

    how to script sql 2000 jobs at once as individual files?

    Can anyone help save me some time? I need to script all jobs on the server as separate files.

    Thanks in advance

  • February 19, 2009 at 11:56 am

    #946984

    Enterprise Manager -> management -> jobs right click select all task and under it generate sql script.

    SQL DBA.

  • February 19, 2009 at 11:58 am

    #946990

    well, I knew that 🙂 i am askin if there is a way to do all of them at once?

  • February 19, 2009 at 12:01 pm

    #946994

    pobeda (2/19/2009)

    well, I knew that 🙂 i am askin if there is a way to do all of them at once?

    ooops.... I missed " individual " from your question.

    SQL DBA.

  • February 19, 2009 at 1:59 pm

    #947137

    pobeda (2/19/2009)

    how to script sql 2000 jobs at once as individual files?

    Can anyone help save me some time? I need to script all jobs on the server as separate files.

    Thanks in advance

    You can use SQLDMO object to script SQL Server objects.....

    Set objSQL = CreateObject("SQLDMO.SQLServer")

    Set fso = CreateObject ("Scripting.FileSystemObject")

    DestDir = "C:\Jobs\"

    ' For Trusted Connection

    objSQL.LoginSecure = True

    'For non-trusted connections ---

    ' objSQL.LoginSecure = False

    ' objSQL.Login = "user"

    ' objSQL.Password = "password"

    ' SQL SERVER Name

    objSQL.Connect "SQLSER?VER"

    Set objJob = objSQL.JobServer

    For each job in objJob.Jobs

    sFileName = DestDir & Replace(job.Name, "\", "-") & ".sql"

    job.Script 1203765415, sfileName

    Next

    This is a vb script and can be run in DOS prompt as

    cscript scriptname.vbs

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • February 20, 2009 at 3:23 pm

    #947999

    In management studio under object explorer details pane double-click on Jobs and then select all jobs. After tht right click and ....here you go with create script option.

    MJ

  • February 20, 2009 at 7:16 pm

    #948065

    Ahmad, what is the numeric value on this line indicating?

    job.Script 1203765415, sfileName

  • February 21, 2009 at 12:51 pm

    #948206

    tnolan (2/20/2009)

    Ahmad, what is the numeric value on this line indicating?

    job.Script 1203765415, sfileName

    here is the definition of script function

    Function Script([ByVal ScriptType As SQLDMO_SCRIPT_TYPE = SQLDMOScript_PrimaryObject], [ByVal ScriptFilePath], [ByVal Script2Type As SQLDMO_SCRIPT2_TYPE = SQLDMOScript2_Default]) As String

    where SQLDMO_SCRIPT_TYPE in

    Const SQLDMOScript_Aliases = 16384

    Const SQLDMOScript_AppendToFile = 256

    Const SQLDMOScript_Bindings = 128

    Const SQLDMOScript_ClusteredIndexes = 8

    Const SQLDMOScript_DatabasePermissions = 32

    Const SQLDMOScript_Default = 4

    Const SQLDMOScript_DRI_All = 532676608

    Const SQLDMOScript_DRI_AllConstraints = 520093696

    Const SQLDMOScript_DRI_AllKeys = 469762048

    Const SQLDMOScript_DRI_Checks = 16777216

    Const SQLDMOScript_DRI_Clustered = 8388608

    Const SQLDMOScript_DRI_Defaults = 33554432

    Const SQLDMOScript_DRI_ForeignKeys = 134217728

    Const SQLDMOScript_DRI_NonClustered = 4194304

    Const SQLDMOScript_DRI_PrimaryKey = 268435456

    Const SQLDMOScript_DRI_UniqueKeys = 67108864

    Const SQLDMOScript_DRIIndexes = 65536

    Const SQLDMOScript_DRIWithNoCheck = 536870912

    Const SQLDMOScript_Drops = 1

    Const SQLDMOScript_IncludeHeaders = 131072

    Const SQLDMOScript_IncludeIfNotExists = 4096

    Const SQLDMOScript_Indexes = 73736

    Const SQLDMOScript_NoCommandTerm = 32768

    Const SQLDMOScript_NoDRI = 512

    Const SQLDMOScript_NoIdentity = 1073741824

    Const SQLDMOScript_NonClusteredIndexes = 8192

    Const SQLDMOScript_None = 0

    Const SQLDMOScript_ObjectPermissions = 2

    Const SQLDMOScript_OwnerQualify = 262144

    Const SQLDMOScript_Permissions = 34

    Const SQLDMOScript_PrimaryObject = 4

    Const SQLDMOScript_SortedData = 1048576

    Const SQLDMOScript_SortedDataReorg = 2097152

    Const SQLDMOScript_TimestampToBinary = 524288

    Const SQLDMOScript_ToFileOnly = 64

    Const SQLDMOScript_TransferDefault = 422143

    Const SQLDMOScript_Triggers = 16

    Const SQLDMOScript_UDDTsToBaseType = 1024

    Const SQLDMOScript_UseQuotedIdentifiers = -2147483648

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

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

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