INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Scheduling DTS Packages

How to Schedule a Job Programatically with VB by Craftor
Posted: 25 Jan 02

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

'set up all your variables
Dim oJob As New SQLDMO.Job
Dim oJobStep As SQLDMO.JobStep
Dim oJobSchedule As SQLDMO.JobSchedule
Dim oSQLServer As New SQLDMO.SQLServer

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

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

'Add your new job
oSQLServer.JobServer.Jobs.Add oJob

'Inform SQL that you are beginning to alter the job
oJob.BeginAlter

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

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

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

'what the step will do on fail - possible values are:
'QuitWithFailure
'GotoNextStep
'GotoStep
'QuitWithSuccess
'Unknown

oJobStep.OnFailAction = SQLDMOJobStepAction_QuitWithFailure
'what the step will do on success - as above
oJobStep.OnSuccessAction = SQLDMOJobStepAction_QuitWithSuccess

'add the step to the job
oJob.JobSteps.Add oJobStep

'on which step the job must start
oJob.StartStepID = 1

'commit your changes to SQL
oJob.DoAlter

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

'add the schedule to the Job
oJob.JobSchedules.Add oJobSchedule

'commit the changes to SQL
oJob.DoAlter

'apply the job to a specific SQL Server
oJob.ApplyToTargetServer "SQL Server"

'commit the changes to SQL
oJob.DoAlter

'disconect from the database
oSQLServer.DisConnect

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

'******************************************************

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

Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close