Monday, February 07, 2005

How to script a SQL Server a Job with Visual Basic and SQL-DMO...

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: