Hi Guys,
I'm Hoping some one could help me out, I'm in need of creating a web interface to monitor scheduled SQL Jobs, both on sql 2000 & 2005.
I'm new to asp, Can anyone point me in the right direction.Really appreciate it.
Regards
This should get you started for SQL2005.
<%@. Page Language="VB" EnableTheming="False" %><%@. ImportNamespace="System.Data" %><%@. ImportNamespace="System.Data.SqlClient" %><!DOCTYPE html PUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><script runat="server">Protected Sub Page_Load(ByVal senderAs Object,ByVal eAs System.EventArgs)If Not Page.IsPostBackThen JobHistory_GridView.DataSource = FJobHistory_GridView() JobHistory_GridView.DataBind()End If End Sub Function FJobHistory_GridView()As System.Data.IDataReaderDim SelectConnAs New SqlConnection("SQL_Server_ConnectionString_With_User_That_Can_Access_MSDB_Database")Dim SelectCmdAs New SqlCommand() SelectCmd.CommandText ="msdb.dbo.sp_help_jobactivity" SelectCmd.CommandType = CommandType.StoredProcedure SelectCmd.Connection = SelectConn SelectConn.Open()Dim dataReaderAs IDataReader = SelectCmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)Return dataReaderEnd Function </script><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>SQL Job History</title></head><body> <form id="form1" runat="server"> <div> <asp:GridView ID="JobHistory_GridView" runat="server" AutoGenerateColumns="True"> </asp:GridView> </div> </form></body></html>|||Thanks a mil Brad,
This will def come in handy, I'm new at this company, i've noticed that they already have a web interface set up to monitor sql jobs, at present it is setup to monitor jobs on sql 2000, I need to now get this working with sql2005, I've configured everything neccessary on the config & aspx pages. I get a connection to sql 2005, I know this because as part of the monitoring tool you are able to view proccesser info as well, I can view this information of the the sql 2005 server however it does not display the jobs. (Just Blank)
Any idea why this might be? I could post the code if that would help.
Thanks again for your assistance.
BR
|||Try this for the gridview instead
<asp:GridView ID="JobHistory_GridView" runat="server" AutoGenerateColumns="False" DataKeyNames="session_id,job_id"> <Columns> <asp:BoundField DataField="session_id" HeaderText="session_id"ReadOnly="True" /> <asp:BoundField DataField="job_id" HeaderText="job_id"ReadOnly="True" /> <asp:BoundField DataField="job_name" HeaderText="job_name" /> <asp:BoundField DataField="run_requested_date" HeaderText="run_requested_date" /> <asp:BoundField DataField="run_requested_source" HeaderText="run_requested_source" /> <asp:BoundField DataField="queued_date" HeaderText="queued_date" /> <asp:BoundField DataField="start_execution_date" HeaderText="start_execution_date" /> <asp:BoundField DataField="last_executed_step_id" HeaderText="last_executed_step_id" /> <asp:BoundField DataField="last_executed_step_date" HeaderText="last_executed_step_date" /> <asp:BoundField DataField="stop_execution_date" HeaderText="stop_execution_date" /> <asp:BoundField DataField="next_scheduled_run_date" HeaderText="next_scheduled_run_date" /> <asp:BoundField DataField="job_history_id" HeaderText="job_history_id" /> <asp:BoundField DataField="message" HeaderText="message" /> <asp:BoundField DataField="run_status" HeaderText="run_status" /> <asp:BoundField DataField="operator_id_emailed" HeaderText="operator_id_emailed" /> <asp:BoundField DataField="operator_id_netsent" HeaderText="operator_id_netsent" /> <asp:BoundField DataField="operator_id_paged" HeaderText="operator_id_paged" /> </Columns> </asp:GridView>If that doesnt work, post your code.
