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:
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, 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.
Step 3
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.
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:
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
objSQLServer.Executive.Tasks(CStr(Task)).Remove
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:
The task created above will run only once because we have passed the
current date to the ExecutionDate property.
Compiling the Project
After entering the code in the class module, compile the project to make a
DLL. It would be better to reference the "Microsoft Active Server Pages
Object Library" in the project so ASP will not give us an "Out of Process
Component" error message if we try to call the component from any ASP page.
In a nutshell, what this means is that this component will know that it
needs to access the Active Server Pages DLL (asp.dll) to run. Finally,
compile the project as a DLL. Voila! You have created an ASP component that
can add and remove SQL Scheduler tasks.
Usage
Consider a scenario in which you create an e-commerce application. You have
used SQL Server for data-storage purposes. Users come and register on your
site before doing any shopping on your site, and they place orders, but
leave your site without checking out.
You should be able to delete the items they placed in their shopping
basket. It would be tedious to manually check the database for valid
records and delete the unwanted records. Instead, use the component you
just created and create a task that would search the database for invalid
or unwanted records and would delete them automatically after a certain
amount of time.
Similarly, you can create and run a VB application once a month that would
remove the accomplished tasks from the SQL Server. Alternately, you could
create a component that would check and remove the unwanted tasks from the
SQL Server, create a new task manually, and call this component using the
extended stored procedures provided with SQL Server to remove the unwanted
tasks from the SQL Server. SQL Server 6.5 and later provides the capability
of loading and executing COM objects through a set of OLE Automation stored
procedures or through extended stored procedures.
Code Details
VB Component source code and ASP files to test the component are provided
with this article. A compiled DLL is also provided. Directly plug in the
DLL in your Web application to test the component functionality. Source
code is also provided so that you can play with the code yourself and try
to add some more features in the component. ASP files are provided to test
the component in ASP. Create and remove tasks through these files. To run
the ASP files, create a new Web application and include all the ASP files
in the new application, and open the index file, tm_demo1.asp, in your
browser to test the functionality.
Summary
Creating ASP Component to manage SQL Server remotely is fairly easy. It
only requires a basic knowledge of ASP, VB, and SQL Server. The article
shows how easy it is to create a component that harnesses the power of
SQL-DMO and manages the SQL tasks remotely.
About the Author
S.S. Ahmed is a senior software engineer in a software development company
that specializes in Web application development. To contact Ahmed with
questions or comments, email him at ss_ahmed1@hotmail.com.
Proposion N2N connects Microsoft .NET applications to Lotus Notes and Lotus Domino databases. This ADO.NET managed data provider allows you to perform blindingly fast queries and updates of Notes data from ASP.NET pages, .NET web services, Windows, or Mobile applications. An innovative SQL-like query language leverages the unique features of Notes and makes collaborative software accessible to relational database programmers.
Unlike text-based file formats image files aren't made up of words, which makes searching for an image file by keyword difficult. Instead of being able to simply open the file to see what it contains, we're stuck looking at the text around it and other metadata to determine the image's meaning. In this article, Ziran Sun shows you how to build a simple database-based image keyword system that allows you to associate keywords with images and use these keywords to make finding images easier. [Read This Article][Top]
In the second part of of his article on using MySQL with ASP.NET, Ziran Sun covers how to add a new MySQL user to the database server, assign the user the appropriate permissions, connect to the database, and build a simple ASP.NET page to perform a query. [Read This Article][Top]
Back in the days of classic ASP, if you were building a database-driven
web site, your choice was either to invest a lot of money to get a copy of Microsoft SQL Server
(or some other enterprise-ready database) or invest a lot of time finding a way to deal with the
performance and scalability limitations of Microsoft Access. Luckily these days there's
another viable alternative: MySQL. [Read This Article][Top]
Moving or copying a SQL Server database from one machine to another requires a lot of preparation in order to ensure a smooth transfer. In this article, Dina Fleet Berry examines the different methods and highlights the different issues associated with each of them. [Read This Article][Top]
There are many times when using SQL Server 2000 Query Analyzer to debug SQL statements is a better choice than debugging in Visual Studio .NET. In this article, Dina Fleet Berry explains why and walks you through the debugging process step-by step. [Read This Article][Top]
As a follow up to his article on retrieving objects from SQL Server using SQLXML and serialization, Gianluca Nuzzo discusses saving objects back to SQL Server using a schema definition file and updategrams. [Read This Article][Top]
One area that stands out when comparing ADO.NET 1.x to ADO.NET 2.0 is transaction processing. Bill Ryan shows just how easy transaction processing has become with the TransactionScope object in ADO.NET 2.0. [Read This Article][Top]
Developers often use brute force coding to marshal data between the GUI and application objects. In this article, Luther Stanton explains how to use .NET's out-of-the box data-binding functionality to make this job much easier. [Read This Article][Top]
Learn how to create a console application to queue a message in Microsoft Message Queuing (MSMQ) and then use an extended stored procedure to call the console application from a SQL Server trigger. [Read This Article][Top]
Connection pooling increases the performance of Web applications by reusing active database connections instead of creating a new connection with every request. This article shows how to monitor the connection pool, diagnose a potential problem, and apply the appropriate fix. [Read This Article][Top]
Mailing List
Want to receive email when the next article is published? Just Click Here to sign up.
