Technical Article

DTS package to read status of all jobs

,

This DTS package reads a list of servers from an XML configuration file, connects to each server, and writes a list of all failed jobs to a web page on the web server.  Each morning, I just need to open my browser and I can see the status of all 42 of our servers.

This is the script.  The XML file follows.
'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************
Dim xmlServers

Function Main()

' Read in the server list
InitServers

' Get the job status and write the web page
GetJobStatus

Main = DTSTaskExecResult_Success
End Function

Sub GetJobStatus()
Dim xmlServer
Dim ServerName
Dim ConnectionString
Dim cn
Dim rs
Dim SQL
Dim JobName
Dim ErrorMessage
Dim RunDate
Dim RunTime
Dim OK
Dim fso
Dim ts

Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.OpenTextFile("\\DBAWEB01\SQL$\SQLJobStatus.htm", 2,-1)
Set cn = CreateObject("ADODB.Connection")

' If this is monday, the include the whole weekend
RunDate = Now()
if DatePart( "w", RunDate ) = 2 then
    RunDate = DateAdd("d",-3,RunDate)
else
    RunDate = DateAdd("d",-1,RunDate)
end if


ts.WriteLine "<HTML><HEAD>" & _
    "<title>My Company</title>" & _
    "</HEAD>" 

ts.WriteLine "<BODY>" & _
    "<H3>SQL Server Job Failures</H3>" & _
    "<H3>" & FormatDateTime(Now(),vbShortDate) & "</H3>" & _
    "<H3>from " & FormatDateTime(RunDate,vbShortDate) & " to date</H3>" & _
    "<TABLE border=1><TR><TH>Server</TH><TH>Job Name</TH><TH>Message</TH><TH>Failure Date</TH></TR>"

RunDate = Year(RunDate) * 10000 + Month(RunDate) * 100 + Day(RunDate)

SQL = "select j.name, jh.message, jh.run_date, jh.run_time" & _
    " from sysjobs j" & _
    " inner join sysjobhistory jh" & _
    " on j.job_id = jh.job_id" & _
    " and jh.step_id = 0" & _
    " and run_status = 0" & _
    " and run_date >= " & RunDate & _
    " where j.enabled = 1" & _
    " order by j.name, jh.run_date, jh.run_time "

For Each xmlServer In xmlServers.selectNodes("//Servers/Server")
    ServerName = xmlServer.getAttribute("Name")
        
    ConnectionString = "provider=sqloledb;server=" & ServerName & ";database=msdb;"
    
    If xmlServer.getAttribute("IntegratedSecurity") = "Yes" Then
        ConnectionString = ConnectionString & "Trusted_Connection=yes"
    Else
        ConnectionString = ConnectionString & "UID=" & _
            xmlServer.getAttribute("UID") & ";PWD=" & _
            xmlServer.getAttribute("PWD")
    End If
    
    ts.WriteLine "<TR><TD>" & ServerName & "</TD>"
    
    On Error Resume Next
    cn.Open ConnectionString
    If Err.Number = 0 Then
        On Error GoTo 0
        
        Set rs = cn.Execute(SQL)
        Do Until rs.EOF
            
            
            
            If Not IsNull(rs("message").Value) Then
                JobName = rs("name").Value
                ErrorMessage = rs("message").Value
                RunDate = DateSerial(rs("run_date").Value / 10000, rs("run_date").Value / 100 Mod 100, rs("run_date").Value Mod 100)
                RunTime = TimeSerial(rs("run_time").Value / 10000, rs("run_time").Value / 100 Mod 100, rs("run_time").Value Mod 100)

                ts.WriteLine "<TR><TD>" & ServerName & "</TD><TD>" & JobName & _
                    "</TD><TD>" & ErrorMessage & _
                    "</TD><TD>" & FormatDateTime(RunDate,vbshortdate) & _
                    "  " & FormatDateTime(RunTime, vbShortTime) & "</TD>"
            End If
            ts.WriteLine "</TR>"
            rs.MoveNext
        Loop
        
        rs.Close
        cn.Close
    Else
        ts.WriteLine "<TD>Unable to connect:" & Err.Description & "</TD></TR>"
    End If
    On Error GoTo 0
Next

ts.WriteLine "</TABLE></BODY></HTML>"
ts.Close
Set ts = Nothing
Set fso = Nothing

End Sub

Sub InitServers()

set xmlServers = createObject("MSXML.DOMDocument")

xmlServers.Load "\\DEVSQL01\dts\dba\SQLServerJobs.ini"

End Sub


This is the XML configuration file (SQLServerJobs.ini):

<Servers>
  <Server Name="DEVSQL01"  IntegratedSecurity='Yes' />
  <Server Name="DEVSQL02"  IntegratedSecurity='Yes' />
  <Server Name="DEVSQL03"  IntegratedSecurity='Yes' />
  <Server Name="DEVSQL04"  IntegratedSecurity='Yes' />
  <Server Name="DEVSQL05"  IntegratedSecurity='Yes' />
  <Server Name="QASQL01"   IntegratedSecurity='Yes' />
  <Server Name="QASQL02"   IntegratedSecurity='Yes' />
  <Server Name="QASQL03"   IntegratedSecurity='Yes' />
  <Server Name="QASQL04"   IntegratedSecurity='Yes' />
  <Server Name="QASQL05"   IntegratedSecurity='Yes' />
  <Server Name="PRODSQL01" IntegratedSecurity='Yes' />
  <Server Name="PRODSQL02" IntegratedSecurity='Yes' />
  <Server Name="PRODSQL03" IntegratedSecurity='Yes' />
  <Server Name="PRODSQL04" IntegratedSecurity='Yes' />
  <Server Name="PRODSQL05" IntegratedSecurity='Yes' />
  <Server Name="PRSQL01"   UID='MSDB_READ_ONLY_USER_ID' PWD='Read0nlY' />
</Servers>

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Related content

Technical Article

Shows Tables for a Given Database from DOS

If you are using OSQL or ISQL from DOS querys are really hard to use, because the result is to big for the screen. With this procedure, you can especify from wich column to wich column show the list of tables. For Example: proc_tables master, 3, 10 will show you only a short list of […]

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2001-10-28

753 reads

Technical Article

Run a Script Against All User Databases

Now you can run administrative T-SQL tasks against all your databases in one shot. Very usefull for environments that have a seperate database for each client, ASP's, etc. It's pretty straight forward and can be used for almost any task. This example Looks for a table and prints the database name and selects from the […]

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2001-10-21

3,111 reads

Technical Article

Find Longstanding Open Transactions

Ever forget to commit a transaction and then find out hours later that there is deadlocked transactions all over your database server? Worse yet has one of your coworkers done this to your database server? Never again, this stored procedure will net send the machine that has an open transaction, and send you (The DBA) […]

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4 (1)

You rated this post out of 5. Change rating

2001-10-08

9,099 reads