Skip to content

Instantly share code, notes, and snippets.

@ryanatwork

ryanatwork/browse.asp

Created August 24, 2011 05:45
Show Gist options
  • Save ryanatwork/1167382 to your computer and use it in GitHub Desktop.
Save ryanatwork/1167382 to your computer and use it in GitHub Desktop.
Download ZIP
Browse SQL Server Jobs
Raw
browse.asp
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
Dim strConn
strConn = "YOUR CONNECTION STRING HERE"
Dim Conn
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open(strConn)
Dim cmdDBStatus
Dim rsData
' create the objects
Set cmdDBStatus = Server.CreateObject("ADODB.Command")
With cmdDBStatus
.ActiveConnection = strConn
.CommandText = "procDBStatusSelect"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue)
.Execute
Set DBStatusList = .Execute
End With
%>
<html>
<head>
<title>DB Status Management - Browse DBStatus</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script LANGUAGE="JavaScript" SRC="overlib.js"></script>
</head>
<body>
<h2></h2>
<center>
<h2><font face="Arial, Helvetica, sans-serif">Database Job Status</h2>
</center>
<form action="browse.asp" method="get" name="browseForm" id="browseForm">
<center>
<table width="400" border="1" cellpadding="5">
<tr bgcolor="#FFFFFF">
<td width="12%"><font face="Arial, Helvetica, sans-serif">Status</font></td>
<td width="12%"><font face="Arial, Helvetica, sans-serif">Server</font></td>
<td width="17%"><font face="Arial, Helvetica, sans-serif">Job Name</font></td>
<td width="15%"><font face="Arial, Helvetica, sans-serif">Job Description</font></td>
<td width="14%"><font face="Arial, Helvetica, sans-serif">Last Run</font></td>
<td width="13%"><font face="Arial, Helvetica, sans-serif">Last Run Date</font></td>
<td width="17%"><font face="Arial, Helvetica, sans-serif">Message</font></td>
</tr>
<%
While (NOT DBStatusList.EOF)
%>
<tr bgcolor="#FFFFFF">
<td width="12%"><center>
<%If DBStatusList("last_run_outcome") = "0" AND NOT ISNULL(DBStatusList("last_run_outcome"))Then%><p>
<img src="_gfx/l_red.gif"> <%Else%><img src="_gfx/l_green.gif"></p>
</center><%End If%> </td>
<td width="12%">
<left><font face="Arial, Helvetica, sans-serif" size=-1>
<%=DBStatusList("originating_server")%></font></center></left>&nbsp;</td>
<td width="17%">
<left><font face="Arial, Helvetica, sans-serif" size=-1><%=DBStatusList("name")%></font></left>&nbsp;</td>
<td width="15%">
<left><font face="Arial, Helvetica, sans-serif" size=-1>
<%=DBStatusList("description")%></font></left>&nbsp;</td>
<td width="14%"><center><font face="Arial, Helvetica, sans-serif" size=-1>
<%If DBStatusList("last_run_outcome") = "0" Then Response.Write("Failed") Else Response.Write("Succeeded") End If%></font></center>
</td>
<td width="13%">
<left><font face="Arial, Helvetica, sans-serif" size=-1>
<%=DBStatusList("last_run_date")%></font></left>&nbsp;</td>
<td width="17%">
<left><font face="Arial, Helvetica, sans-serif" size=-1>
<%=DBStatusList("last_outcome_message")%></font></left>&nbsp;</td>
</tr>
<%
DBStatusList.MoveNext()
Wend
%>
</table>
</center>
</td>
</tr>
</table>
</td>
</tr>
</table>
</form>
</font>
</body>
Raw
jobstatus.sql
CREATE VIEW dbo.JobStatus
AS
SELECT msdb.dbo.sysjobs.originating_server, msdb.dbo.sysjobs.name, msdb.dbo.sysjobs.description, msdb.dbo.sysjobservers.last_run_outcome,
msdb.dbo.sysjobservers.last_outcome_message,
Convert(varchar(20),
cast((Stuff((stuff(msdb.dbo.sysjobhistory.run_date, 7, 0, '/')), 5, 0, '/') +' '+
Stuff(stuff(msdb.dbo.sysjobhistory.run_time, len(msdb.dbo.sysjobhistory.run_time)-1, 0, ':'), len(msdb.dbo.sysjobhistory.run_time)-3, 0, ':')) as datetime), 100)
as Last_run_date
FROM msdb.dbo.sysjobs INNER JOIN
msdb.dbo.sysjobservers ON msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobservers.job_id
innER JOIN msdb.dbo.sysjobhistory on msdb.dbo.sysjobhistory.job_id = msdb.dbo.sysjobs.job_id
WHERE msdb.dbo.sysjobs.enabled = 1
and msdb.dbo.sysjobhistory.instance_id in (select max(instance_id) from msdb.dbo.sysjobhistory where step_id = 0 group by job_id)
UNION
SELECT 'GALEX', GSJ.name, GSJ.description, GSJS.last_run_outcome, GSJS.last_outcome_message, Convert(varchar(20),
cast((Stuff((stuff(GSJH.run_date, 7, 0, '/')), 5, 0, '/') +' '+
Stuff(stuff(GSJH.run_time, len(GSJH.run_time)-1, 0, ':'), len(GSJH.run_time)-3, 0, ':')) as datetime), 100)
as Last_run_date
FROM GALEX.msdb.dbo.sysjobs AS GSJ INNER JOIN
GALEX.msdb.dbo.sysjobservers AS GSJS ON GSJ.job_id = GSJS.job_id
INNER JOIN GALEX.msdb.dbo.sysjobhistory as GSJH on GSJH.job_id = GSJ.job_id
WHERE GSJ.enabled = 1
and GSJH.instance_id in (select max(instance_id) from GALEX.msdb.dbo.sysjobhistory where step_id = 0 group by job_id)
UNION
SELECT ssJ.originating_server, ssJ.name, ssJ.description, ssJS.last_run_outcome, ssJS.last_outcome_message, Convert(varchar(20),
cast((Stuff((stuff(SSJH.run_date, 7, 0, '/')), 5, 0, '/') +' '+
Stuff(stuff(SSJH.run_time, len(SSJH.run_time)-1, 0, ':'), len(SSJH.run_time)-3, 0, ':')) as datetime), 100)
as Last_run_date
FROM SS2.msdb.dbo.sysjobs AS ssJ INNER JOIN
SS2.msdb.dbo.sysjobservers AS ssJS ON ssJ.job_id = ssJS.job_id
INNER JOIN SS2.msdb.dbo.sysjobhistory as ssJH on ssJH.job_id = ssJ.job_id
WHERE ssJ.enabled = 1
and ssJH.instance_id in (select max(instance_id) from SS2.msdb.dbo.sysjobhistory where step_id = 0 group by job_id)
Raw
procDBStatusSelect.sql
CREATE PROCEDURE [dbo].[DBStatusSelect] AS
begin
select * from JobStatus
end
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment