server15 Seconds : ASP AND SQL-DMO:Create a Server Component Encompassing the SQL-DMO Functionality
S.S. Ahmed
10/30 /2000

Introduction

Before starting, let's shed some light on the SQL Distributed Management Objects (SQL-DMO). ASP gets its
functionality by using server components. In ASP we can combine scripts, HTML, and reusable server
components to create scalable Web applications. These server-side ActiveX components can be developed in
many languages, such as VC++, Java, and Visual Basic (VB).


click here to download source code used in this article
ftp://ftp.15seconds.com/001031.zip

I have selected VB to develop a component that will be used in ASP scripts to harness the power of SQL-
DMO. SQL-DMOs are OLE automation-compatible COM objects. These objects and a set of properties and methods
are used to write programs to administer multiple SQL Servers distributed across a network. Also, SQL-DMO
is the foundation of SQL Enterprise Manager. In fact, SQL-DMO is a very powerful object model of SQL
Server management. The obvious advantage of using a component encompassing SQL-DMO functionality is that
you can manage your SQL Server from anywhere in the world.

Although, SQL-DMO is a complete set of objects and methods to manage the SQL Server remotely, in this
article, we will only see how to add and remove SQL tasks in the Task Scheduler. I decided to write this
article because I couldn't find a good article about using SQL-DMO in ASP. The article, which details how
to leverage VB to create the ASP component, uses the following technologies:


Visual Basic 6
SQL Server


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:


Create a SQL Server object.
Connect to the SQL Server object.
Use the SQL Server object and other contained objects.
Release the SQL Server object.


Step 1

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

The following connects to the SQL Server object:


objSQLServer.Connect Server, Us