The Real Business

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.