• download source files - 23.2 kb

introduction

i would suggest that you read the part 1 of this article before going through this one. the major details of the sqldmo are discussed in the part 1 of this article. we have upgraded the component to be used with sql server 7.0. the code in the previous article worked only with the version 6.5 of the sql server. more enhancements are made in the new component, the new component is capable of deleting the task automatically once the task has been accomplished. in the previous version, the task once created has to be removed manually by executing a method called as “removetask” but there is no need for such method in the new component as it will delete the task upon successful completion. furthermore, earlier the task was executed each time a specific date was reached but now the task will execute only once at a specific date the user will supply to the component. also, we have demonstrated how to add two steps in the same job. in the previous article the job consisted of only one step but the new tasks created with the new component shall consist of more than one job step, that is, user can accomplish more than one job within the same task. now, lets go through the code step by step:

private sub class_initialize()
    
    on error resume next
    
    nl = chr$(13) & chr$(10)
        
    set osqlserver = new sqldmo.sqlserver
    
    osqlserver.logintimeout = 10
    
end sub

the main object is created when the class is initialized, similarly this object shall be deleted from the memory when the class is terminated.

our main method is known as addtask, this method will add a new task to the task scheduler, note we have not passed the parameters directly to function instead we have used the properties to get the input from the user.

public function addtask()


    on error goto errhandler
        
    osqlserver.disconnect

disconnect the server if its already connected. 
        
    if server = "" then
        errdesc = "you must enter server name."
        exit function
    elseif userid = "" then
        errdesc = "you must enter a valid user id"
        exit function
    elseif password = "" then
        password = ""
    end if

get values of important parameters from the user, these values are needed to connect to the sqlserver.

 'connect to the server!
    osqlserver.connect cstr(server), cstr(userid), cstr(password)
    
    dim ojob as new sqldmo.job
    dim idstep as integer

idstep will be used to define the total number of steps to be included in the task.

 'set the schedule name
    ojob.name = jobid

assign a name to the job.

    'objsqlserver.executive.tasks.add ojob
    osqlserver.jobserver.jobs.add ojob

add the newly created job to the job server. the jobserver object exposes attributes associated with sql server agent. sql server agent is responsible for executing the scheduled jobs and notifying operators of sql server error conditions or other sql server execution or job states.

'use the code below to change the task!!!
    ojob.beginalter
    'idstep = 0

initially we have assigned a zero value to the step id. because we intend to add two steps in our task, so we run a loop twice.

    for idstep = 0 to 2
        
        dim ojobstep as sqldmo.jobstep
        set ojobstep = new sqldmo.jobstep