Administer SQL Remotely from ASP (Article and Code Demo)

Category:

Database

Type:

Applications

Difficulty:

Intermediate

Author: ahmed

Version Compatibility: Visual Basic 6, ASP

Instructions: Copy the declarations and code below and paste directly into your VB project.

Declarations:
' (None)

Code:

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). 

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: 
<PRE>
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
</pre>
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 ...