Now, lets go through 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 the 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 its 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 values of important parameters from the user, these values are needed to
connect to the sqlserver. '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 SQL server agent. 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 in our task, so we run a loop twice. For idStep = 0 To 2
Dim oJobStep As SQLDMO.JobStep
Set oJobStep = New SQLDMO.JobStep
Comments