INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Come Join Us!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts
- Keyword Search
- Turn Off Ad Banners
- 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.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
Member Feedback
"...I have tons of books, have book marked tons of tutorials, which have helped, but this forum has answered those "impossible to find" solutions. I am thrilled with this site..."
Geography
Where in the world do Tek-Tips members come from?
|
Microsoft SQL Server: Programming FAQ
|
Scheduling DTS Packages
|
How to Schedule a Job Programatically with VB
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 FAQ Archive
 Email This FAQ To A Friend |
|
 |
|
