I have created a class named "Task." that contains all the code needed to implement the functionality.
This is the code from Task.cls:
Public Function AddTask()
...........................
objSQLServer.DisConnect
objSQLServer.Connect Server, UserID, Password
Dim objTask As SQLOLE.Task
Set objTask = CreateObject("SQLOLE.Task")
'Set the schedule name
objTask.Name = TaskName
objSQLServer.Executive.Tasks.Add objTask
.................................
Case "single_run":
Case 2:
If ExecutionDate = "" Then
ErrDesc = "You must provide the task
execution date."
Exit Function
Else
If IsDate(ExecutionDate) = False Then
ErrDesc = "Please provide
a valid task execution date."
Exit Function
Else
'Set the schedule name
objTask.Name = TaskName
objSQLServer.Executive.Tasks.Add
objTask
'Change the task!
objTask.BeginAlter
objTask.Database = DatabaseName
objTask.Command = CommandText
objTask.FrequencyType
= SQLOLEFreq_OneTime
objTask.ActiveStartDate
= CDate(ExecutionDate)
objTask.DoAlter
End If
End If
If (objTask.CmdExecSuccessCode) Then
ErrDesc = "Failure"
Else
ErrDesc = "Success"
End If
End Function
The class has two main functions named AddTask and RemoveTask.AddTask adds a
new task to the Scheduler. Similarly, RemoveTask removes the task from the Scheduler.
First of all, you will have to include the "Microsoft SQL OLE Object library"
from the references in the Project Menu. Once, you have done that, follow the
steps below:
Step 1 - Create a SQL Server object
The following creates a new SQL Server object:
Dim objSQLServer As SQLOLE.SQLServer
Set objSQLServer = New SQLOLE.SQLServer
The objSQLServer object is an instance of the SQLOLE.SQLServer class. This object represents the SQL Server in which tasks will be added or removed. It's needed in order to move ahead and create another object that will be used to create a new task. Notice this uses the "New" keyword to instantiate the SQLServer object. We could have used the CreateObject function instead, but late binding would have given the app a slower performance. The reference through an early bound variable promotes a better performance.
Step 2 - Connect to the SQL Server object
The following connects to the SQL Server object:
objSQLServer.Connect Server, UserID, Password
Note we have passed three arguments to the Connect method. The first argument is the name of the SQL Server to which you want to connect, the second argument is the User ID required to log on to the SQL Server, and thehe third argument is the password required to log on to the SQL Server. If you provide correct parameters to the Connect method, you will be connected to the SQL Server.
Comments