February 19, 2009 at 11:51 am
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
Enterprise Manager -> management -> jobs right click select all task and under it generate sql script.
SQL DBA.
February 19, 2009 at 11:58 am
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
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
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
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
Ahmad, what is the numeric value on this line indicating?
job.Script 1203765415, sfileName
February 21, 2009 at 12:51 pm
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