How to Schedule a Job Programatically with VB

First off, set a reference to Microsoft SQL-DMO in your VB project. You will need to have SQL Server 7 installed on your box for this. I haven't tried this under SQL 2000 or SQL 6.5

[color green]'set up all your variables [/color]
Dim oJob As New SQLDMO.Job
Dim oJobStep As SQLDMO.JobStep
Dim oJobSchedule As SQLDMO.JobSchedule
Dim oSQLServer As New SQLDMO.SQLServer

[color green]'this name must be unique on that SQL Server i.e. no two jobs can have the same name [/color]
oJob.Name = "Test SQL Job"

[color green]'connect to your SQL Server, passing the user name and password to log on [/color]
oSQLServer.Connect "SQL SERVER", "USERNAME", "PASSWORD"

[color green]'Add your new job[/color]
oSQLServer.JobServer.Jobs.Add oJob

[color green]'Inform SQL that you are beginning to alter the job[/color]
oJob.BeginAlter

Set oJobStep = New SQLDMO.JobStep
oJobStep.Name = "Launch Flow"
[color green]'The step ID must be an integer (starting at 1) and must be unique for that job[/color]
oJobStep.StepID = 1

[color green]'Must be a valid database on the server[/color]
oJobStep.DatabaseName = "master"
[color green]'How the job command will be executed[/color]
oJobStep.SubSystem = "TSQL"

[color green]'What will be executed in 'TSQL' - here shelling a simple exe[/color]
oJobStep.Command = "xp_cmdshell 'c:\ISJTemplates\LaunchE.exe'"

[color green]'what the step will do on fail - possible values are:
'QuitWithFailure
'GotoNextStep
'GotoStep
'QuitWithSuccess
'Unknown[/color]
oJobStep.OnFailAction = SQLDMOJobStepAction_QuitWithFailure
[color green]'what the step will do on success - as above[/color]
oJobStep.OnSuccessAction = SQLDMOJobStepAction_QuitWithSuccess

[color green]'add the step to the job[/color]
oJob.JobSteps.Add oJobStep

[color green]'on which step the job must start[/color]
oJob.StartStepID = 1

[color green]'commit your changes to SQL[/color]
oJob.DoAlter

Set oJobSchedule = New SQLDMO.JobSchedule
oJobSchedule.Name = "Launch Flow"
[color green]'the start date is the year followed by the month, followed by the date - this has to be represented as a Long[/color]
oJobSchedule.Schedule.ActiveStartDate = 20020125
[color green]'the time is represented on a 24 hour clock - hours, minutes, seconds - also as a Long[/color]
oJobSchedule.Schedule.ActiveStartTimeOfDay = 143000

[color green]'add the schedule to the Job[/color]
oJob.JobSchedules.Add oJobSchedule

[color green]'commit the changes to SQL[/color]
oJob.DoAlter

[color green]'apply the job to a specific SQL Server[/color]
oJob.ApplyToTargetServer "SQL Server"

[color green]'commit the changes to SQL[/color]
oJob.DoAlter

[color green]'disconect from the database[/color]
oSQLServer.DisConnect

Set oJob = Nothing
Set oJobStep = Nothing
Set oJobSchedule = Nothing
Set oSQLServer = Nothing

[color green]'******************************************************[/color]

Most of this was taken from MSDN and the SQL DMO help. I have tested this code but take no responsibility for its reliability in a production environment

Hope this helps all you out there that struggled with it like I did

Craftor