Let's review the code step by step:
Private Sub Class_Initialize()
On Error Resume Next
NL = Chr$(13) & Chr$(10)
Set oSQLServer = New SQLDMO.SQLServer
oSQLServer.LoginTimeout = 10
End Sub
The main object is created when the class is initialized. Similarly this object shall be deleted from memory when the class is terminated.
Our main method is known as AddTask. This method will add a new task to the task scheduler. Note we have not passed the parameters directly to function, instead we have used the properties to get the input from the user.
Public Function AddTask()
On Error GoTo errhandler
oSQLServer.DisConnect
Disconnect the server if it's already connected.
If Server = "" Then
ErrDesc = "You must enter server name."
Exit Function
ElseIf UserID = "" Then
ErrDesc = "You must enter a valid User ID"
Exit Function
ElseIf Password = "" Then
Password = ""
End If
Get the values of important parameters from the user. These values are needed to connect to the SQL Server.
'Connect to the server!
oSQLServer.Connect CStr(Server), CStr(UserID), CStr(Password)
Dim oJob As New SQLDMO.Job
Dim idStep As Integer
IdStep will be used to define the total number of steps to be included in the task.
'Set the schedule name
oJob.Name = JobID
Assign a name to the job.
'objSQLServer.Executive.Tasks.Add oJob
oSQLServer.JobServer.Jobs.Add oJob
Add the newly created job to the job server. The jobserver object exposes attributes associated with the SQL Server agent. The SQL Server agent is responsible for executing the scheduled jobs and notifying operators of SQL Server error conditions or other SQL Server execution or job states.
'Use the code below to change the task!!!
oJob.BeginAlter
'idStep = 0
Initially we have assigned a zero value to the step id. Because we intend to add two steps to our task, we run the following loop.
For idStep = 0 To 2
Dim oJobStep As SQLDMO.JobStep
Set oJobStep = New SQLDMO.JobStep
We have created a new job step object in the statements above. The JobStep object exposes the attributes of a single SQL Server agent executable job step. SQL Server Agent jobs contain one or more execution units called "steps." Each job step contains a textual command, a type of execution that specifies command interpretation, and logic that determines the behavior of the job if the step succeeds or fails.
idStep = idStep + 1
oJobStep.Name = JobID & idStep
oJobStep.StepID = idStep
'Set the job step executable subsystem.
oJobStep.SubSystem = "TSQL"
The SubSystem property specifies the SQL Server Agent execution subsystem used to interpret job step task-defining text.
If DatabaseName <> "" Then
oJobStep.DatabaseName = DatabaseName
Else
oJobStep.DatabaseName = "yourdatabase"
End If
If the user fails to pass the database name from the front end, then the component will pick up the hard-coded database name provided that you have hard-coded the database name in your code.
If idStep = "1" Then
If CommandText <> "" Then
oJobStep.Command = CommandText
Else
oJobStep.Command = "select * from table1"
oJobStep.OnSuccessAction = SQLDMOJobStepAction_GotoNextStep
End If
Else
oJobStep.StepID = 2
If Commandtext2 <> "" Then
oJobStep.Command = Commandtext2
Else
oJobStep.Command = "delete from table2"
oJobStep.OnSuccessAction = SQLDMOJobStepAction_QuitWithSuccess
End If
End If
We have added two commands to the jobs, one will return all the records from the table and the second will delete all the records from the particular table. This has been done just to give you an example. You can do whatever you want with your database tables by passing the command text either from the front end or by hard-coding the command text in the code as seen above.
oJob.JobSteps.Add oJobStep
Next
Add the individual job step to the JobSteps collection.
'Set the Target Server
oJob.ApplyToTargetServer (CStr(Server))
The ApplyToTargetServer method adds an execution target to the list of targets maintained for the referenced SQL Server Agent job.
Now, here comes the important part, the scheduling of the job. The job has been created, but now we have to schedule the job so that it runs at a specific date and time. The JobSchedule object exposes the attributes of a single SQL Server Agent executable job schedule.
Dim oJobSchedule As SQLDMO.JobSchedule
Set oJobSchedule = New SQLDMO.JobSchedule
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
'Schedule the task!
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
You can calculate any time and date for your task to start executing. It solely depends on your choice or requirement. We have calculated the year, month, and day separately.
Dim startyear, startmonth, startday
'Indicate execution scheduled for every day by using
'the FrequencyType and FrequencyInterval properties.
oJobSchedule.Name = JobID
oJobSchedule.Schedule.FrequencyType = SQLDMOFreq_OneTime
We want to execute the task only once so we have set the frequency type to one time.
'Set the ActiveStartDate to indicating the date on
'which the schedule becomes active. Start date is
'today's date
Dim mydate
mydate = DateAdd("h", CInt(Num_Of_Hours), Now())
Dim hr, min, sec
hr = Hour(mydate)
min = Minute(mydate)
sec = Second(mydate)
Dim mytime
mytime = hr & min & sec
startyear = DatePart("yyyy", mydate)
startmonth = DatePart("m", mydate)
startday = DatePart("d", mydate)
If Len(startmonth) < 2 Then startmonth = "0" & startmonth
If Len(startday) < 2 Then startday = "0" & startday
oJobSchedule.Schedule.ActiveStartDate = startyear & startmonth & startday
The ActiveStartDate property indicates the first effective date for a schedule.
'Set the ActiveStartTimeOfDay property to indicate the
'scheduled execution time on each day
oJobSchedule.Schedule.ActiveStartTimeOfDay = mytime
The ActiveStartTimeOfDay property indicates the start time for a certain day's schedule.
'Indicate that the schedule never expires
oJobSchedule.Schedule.ActiveEndDate = SQLDMO_NOENDDATE
oJobSchedule.Schedule.ActiveEndTimeOfDay = SQLDMO_NOENDTIME
Similarly, we have to provide the ActiveEndDate and time for the job. We have set these properties to "SQLDMO_NOENDDATE" and "SQLDMO_NOENDTIME" which means that the job will never expire until is executed.
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
'Add task to scheduler
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
oJob.JobSchedules.Add oJobSchedule
Now here is the clever part -- how to remove the task from the scheduler automatically. There is a property named as DeleteLevel that controls post-execution processing for SQL Server Agent jobs.
'Automatically delete job after successful completion
oJob.DeleteLevel = SQLDMOComp_Success
If the job is successful, then we delete the job from the scheduler.
oJobStep.OnFailAction = SQLDMOJobStepAction_QuitWithFailure
If SQLDMOJobStepAction_QuitWithFailure = True Then
ErrDesc = "Failure"
Else
ErrDesc = "Success"
End If
oJob.StartStepID = 1
'Alter the job
oJob.DoAlter
'Clear references to the created objects
Set oJob = Nothing
Set oJobStep = Nothing
Set oJobSchedule = Nothing
Exit Function
errhandler:
'Clear references to the created objects
Set oJob = Nothing
Set oJobStep = Nothing
Set oJobSchedule = Nothing
ErrDesc = "Failure: " & "'" & Err.Source & "'" & " " & Err.Number & " " & Err.Description
End Function
The rest of the code is similar to the code used in Part 1 of this article. It explains how to destroy the object when the class terminates. Compile the DLL and access the component either in your Visual Basic (VB) project or ASP code. It works fine and efficiently. You can customize the component to add more features or to change the functionality according to your own needs.
