How to script a SQL Server a Job with Visual Basic and SQL-DMO...
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 = "<<>>"
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 = "<<>>" & 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
No comments:
Post a Comment