ASP AND SQL-DMO Create a Server Component-IDC资讯中心

15 seconds : asp and sql-dmo:create a server component encompassing the sql-dmo functionality

s.s. ahmed

10/30 /2000

introduction

before starting, lets 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 couldnt 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. its 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 objects 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, its 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.beginalter

objtask.database = databasename

objtask.command = commandtext

objtask.frequencytype = sqlolefreq_onetime

objtask.activestartdate = cdate(executiondate)

objtask.doalter

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 components 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 statements 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, its necessary to set certain properties before calling this method. have a look at the

following statements:

dim objtaskmanager

set objtaskmanger = server.createobject("taskmanager.task")

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

objtaskmanager.addtask

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.

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.

back to article

ASP AND SQL-DMO Create a Server Component

相关推荐

热门标签

热门文章

分类目录