SQL Distributed Management Objects

Step 3...

Step 3 - Use the SQL Server object and other contained objects

Once you are connected to the SQL Server, you can make use of the newly created object's methods and properties to accomplish the task. Our task is to create a new task in the SQL Scheduler so we are going to create a new task, and later we will set certain properties of this object.

Dim objTask As SQLOLE.Task
Set objTask = CreateObject("SQLOLE.Task")

Now that the task object has been created, we need to add the task to the scheduler. Define the task name by calling the Name property of the Task object, and then add this task to the SQL Server Scheduler.

objTask.Name = TaskName
objSQLServer.Executive.Tasks.Add objTask

After the task has been added to the scheduler, it's time to add some commands in the newly created task. You may want to create and run a task to delete particular records from a table at a particular time, or you may want to send an email to the site administrator on an exact date of the month. All of this can be done by assigning values to certain properties of the Task object. Look at the below statements:

objTask.Database = DatabaseName
objTask.Command = CommandText

objTask.FrequencyType = SQLOLEFreq_OneTime
objTask.ActiveStartDate = CDate(ExecutionDate)

Before assigning values to the properties, you must call BeginAlter method, which tells the SQL Server that changes are about to be made to the task properties. Actually, each change to a single property is a separate update to SQL Server. We use the BeginAlter method to group multiple property changes into a single unit. Call the DoAlter method to commit the changes made to the object properties. You can also call the CancelAlter method to cancel the unit of property changes.

Assign a valid database name to the "Database" property. This is the database in which you want to execute the task.

objTask.Database = DatabaseName

Pass a valid Transact SQL statement to execute for the task you have created to the "Command" property.

objTask.Command = CommandText

In the original Task code, we assigned a valid value to the FrequencyType property, which is the primary frequency unit of time. More details are included in the source code files accompanying this article. Please refer to the component's source code to see the different uses of the FrequencyType property.

objTask.FrequencyType = SQLOLEFreq_OneTime

The above line of code is meant to run only once, therefore a date is assigned to the ActiveStartDate property. The task will automatically execute on this date. ActiveStartDate is the date before which this task is active. There is another property which I think should be mentioned here, ActiveEndDate, the date and time after which the task is active.

objTask.ActiveStartDate = CDate(ExecutionDate)

Using the code provided, you could create a task that would run on a daily basis, hourly basis, or only once on the date provided as a parameter. By viewing the attached source code in Visual Basic, a reader can see that it is thoroughly commented so the reader can understand the statements without frequently pressing F1 to discover a statement's meaning. The following is the "RemoveTask" function that removes the named task from the scheduler:

Public Function RemoveTask(ByVal Task As Variant)


objSQLServer.Connect Server, UserID, Password

ErrDesc = "The task has been removed."


End Function

Remember, it's necessary to set certain properties before calling this method. Have a look at the following statements:

Dim objTaskManager
Set objTaskManger = server.createobject("TaskManager.Task")

objTaskManager.Server = cstr(request.form("servername"))
objTaskManager.UserID = cstr(request.form("userid"))
objTaskManager.Password = cstr(request.form("password"))
objTaskManager.RemoveTask cstr(request.form("taskname"))

response.write objTaskManager.ErrDesc

Set objTaskManager = Nothing

This is the ASP code that shows how to instantiate the component and call the "RemoveTask" function. Notice that before the function is called, server name, User ID, and Password values are passed to the properties. Use the ErrDesc property to see the status of the function called.

Following is the ASP code needed to add a new task to the SQL Scheduler:

Dim objTaskManager
Set objTaskManager = server.createobject("TaskManager.Task")

objTaskManager.Server = cstr(request.form("servername"))
objTaskManager.UserID = cstr(request.form("userid"))
objTaskManager.Password = cstr(request.form("password"))
objTaskManager.DatabaseName = cstr(request.form("databasename"))
objTaskManager.TaskName = cstr(request.form("taskname"))
objTaskManager.CommandText = cstr(request.form("commandtext"))
objTaskManager.ScheduleType = cint(request.form("scheduletype"))
objTaskManager.ExecutionDate = cstr(request.form("executiondate"))

response.write objTaskManager.ErrDesc
Set objTaskManager = Nothing

The task created above will run only once because we have passed the current date to the ExecutionDate property.

You might also like...


About the author

S.S. Ahmed United States

S.S. Ahmed is a senior IT Professional and works for a web and software development firm. Ahmed is a Microsoft Office SharePoint Server MVP. Ahmed specializes in creating database driven dynamic...

Interested in writing for us? Find out more.


Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Perl - The only language that looks the same before and after RSA encryption.” - Keith Bostic