Administer SQL Server remotely - Part 2

VB_DataBase 专栏收录该内容
5 篇文章 0 订阅
Administer SQL Server remotely - Part 2
By S.S. Ahmed

Create a component to administer SQL server remotely, create tasks that remove themselves from the scheduler after performing the assigned job. 
 Advanced
 vb6, XP, W2K, Win9X
 Posted 5 Nov 2001
Articles by this author
Send to a friend
Printer friendly version
Views: 737  [This is an unedited reader contribution] [Modify this article][Delete this article]
<script defer language=JavaScript src="/script/toolbar.js"></script> <script language=JavaScript> </script>
FAQ
What's New
Lounge
Contribute
Message Boards
7 users have rated this article. result:
2 out of 5.

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


We have created a new job step object in the statements above. The jobstep object exposes the attributes of a single SQL Server agent executable job step. SQL Server Agent jobs contain one or more execution units called steps. Each job step contains a textual command, type of execution that specifies command interpretation, and logic that determines the behaviour of the job if the step succeeds or fails

idStep = idStep + 1
        
        oJobStep.Name = JobID & idStep
        oJobStep.StepID = idStep
        
        'Set the job step executable subsystem.
        oJobStep.SubSystem = "TSQL"

The subsystem property specifies the SQL Server Agent execution subsystem used to interpret job step task-defining text.

 If DatabaseName <> "" Then
           oJobStep.DatabaseName = DatabaseName
        Else
            oJobStep.DatabaseName = "yourdatabase"
        End If

If the user fails to pass the database name from the front end than the component will pick up the hardcoded database name provided that you have hardcoded the database name in your code.

If idStep = "1" Then
            If CommandText <> "" Then
                oJobStep.Command = CommandText
            Else
                oJobStep.Command = "select * from table1"
                oJobStep.OnSuccessAction = SQLDMOJobStepAction_GotoNextStep
            End If
        Else
        
            oJobStep.StepID = 2
            If Commandtext2 <> "" Then
                oJobStep.Command = Commandtext2
            Else
                oJobStep.Command = "delete from table2"
                oJobStep.OnSuccessAction = SQLDMOJobStepAction_QuitWithSuccess
            End If
       End If
       


We have added two commands to the jobs, one will return all the records from the table and the second will delete all the records from the particular table, this has been done just to give you an example, you can do whatever you want with your database tables by passing the command text either from the front end or by hardcoding the command text in the code as seen above.

        oJob.JobSteps.Add oJobStep
    Next

Add the individual job step to the jobsteps collection.

'Set the Target Server
oJob.ApplyToTargetServer (CStr(Server))

The applytotargetserver method adds an execution target to the list of targets maintained for the referenced SQL Server Agent job.

Now, here comes the important part, the scheduling of the job, the job has been created but now we have to schedule the job so that it runs at a specific date and time.

JobSchedule object exposes the attributes of a single SQL Server Agent executable job schedule.

    Dim oJobSchedule As SQLDMO.JobSchedule
    
    Set oJobSchedule = New SQLDMO.JobSchedule
    
    'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
    'Schedule the task!
    'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

You can calculate any time and date for your task to start execution, it solely depends on your choice or requirement. We have calculated the year, month and day separately.

  Dim startyear, startmonth, startday
    
    'Indicate execution scheduled for everyday by using
    'the FrequencyType and FrequencyInterval properties.
    oJobSchedule.Name = JobID
    oJobSchedule.Schedule.FrequencyType = SQLDMOFreq_OneTime


We want to execute the task only once so we have set the frequency type to single time.

 'Set the ActiveStartDate to indicating the date on
    'which the schedule becomes active. Start date is
    'today's date
    
    Dim mydate
    mydate = DateAdd("h", CInt(Num_Of_Hours), Now())
        
    Dim hr, min, sec
    hr = Hour(mydate)
    min = Minute(mydate)
    sec = Second(mydate)
    
    Dim mytime
    mytime = hr & min & sec
    
    startyear = DatePart("yyyy", mydate)
    startmonth = DatePart("m", mydate)
    startday = DatePart("d", mydate)
    
    If Len(startmonth) < 2 Then startmonth = "0" & startmonth
    If Len(startday) < 2 Then startday = "0" & startday
    
    oJobSchedule.Schedule.ActiveStartDate = startyear & startmonth & startday

Activestartdate property indicates the first effective date for a schedule.

'Set the ActiveStartTimeOfDay property to indicate the
    'scheduled execution time on each day
    
    oJobSchedule.Schedule.ActiveStartTimeOfDay = mytime

Activestarttimeofday property indicates the start time of the day for a schedule.

'Indicate that the schedule never expires
    oJobSchedule.Schedule.ActiveEndDate = SQLDMO_NOENDDATE
    oJobSchedule.Schedule.ActiveEndTimeOfDay = SQLDMO_NOENDTIME

Similarly, we have to provide the activeenddate and time for the job. We have set these properties to “SQLDMO_NOENDDATE” and “SQLDMO_NOENDTIME” which means that the job will never expire until is executed.

  'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
    'Add task to scheduler
    'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
    
    oJob.JobSchedules.Add oJobSchedule


Now here is the clever part, that is, how to remove the task from the scheduler automatically, there is a property named as deletelevel which controls post-execution processing for SQL Server Agent jobs.

'Automatically delete job after successful completion
    oJob.DeleteLevel = SQLDMOComp_Success

If the job is successful then we delete the job from the scheduler.
 

   oJobStep.OnFailAction = SQLDMOJobStepAction_QuitWithFailure
    
    If SQLDMOJobStepAction_QuitWithFailure = True Then
        ErrDesc = "Failure"
    Else
        ErrDesc = "Success"
    End If
    
    oJob.StartStepID = 1
    
    'Alter the job
    oJob.DoAlter
    
    'Clear references to the created objects
    Set oJob = Nothing
    Set oJobStep = Nothing
    Set oJobSchedule = Nothing
    
    Exit Function
    
errhandler:
    
    'Clear references to the created objects
    Set oJob = Nothing
    Set oJobStep = Nothing
    Set oJobSchedule = Nothing
    
    ErrDesc = "Failure: " & "'" & Err.Source & "'" & " " & Err.Number & " " & Err.Description
    
End Function


Rest of the code is similar to the one we saw in the first part, destroy the object when the class terminates. Compile the DLL and access the component either in your VB project or ASP code. It works fine and efficiently. You can customize the component to add more features or to change the functionality according to your own needs.
 

Summary
 

There is a difference in SQLOLE and SQLDMO, SQLOLE.DLL library comes with SQL Server 6.5 while SQLDMO.DLL library comes with SQL Server 7.0. You will have to include the correct reference to the library, In case of this article, add the reference to the SQLDMO.DLL reference.
 

About S.S. Ahmed

S.S. Ahmed is a senior software engineer and works for a web and software development firm. Ahmed specializes in creating database driven dynamic web sites. He uses ASP and VB for most of what he develops. Ahmed likes to hop into other tools as well. Ahmed has worked in Assembly language, C, C++, FoxPro, QBasic, All version of Visual Basic (currently experimenting with VB.NET), Visual C, Java, ASP, etc. Ahmed enjoys travelling and has been to many parts of the world. Ahmed can be reached at ss_ahmed1@hotmail.com

  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值