RUNNING JOBS ON SQL SERVER -
OVERVIEW:
This ASPX is designed to run Jobs on SQL Server
(7 or greater).
AUTHOR: BlueShoes
LAST TECHNICAL REVIEW: 31-AUG-2004
ARTICLE:
The code shown below is written in VB.Net.
It will iterate through the jobs on a server (severname,
userid,
password
are obtained
from the web.config file).
In this case it only shows jobs related to the current
website as you can see in the LoadTasks() routine.
The user clicks the job they want to run,
and then the Run button. In the case of one of the jobs
("KPIWeb_DA_Headcount_SAP")
a routine is run RunSAPJob() in which the job itself requires
a parameter and sends this information through xp_cmdshell
to a console app which runs a DLL that processes the data.
XP_CmdShell returns a return value to the job and raises
an error if the dll reports an error.
This should provide enough example of how to work with DMO
to run jobs from an aspx.
SQL-DMO ASPX
Public Class Admin_DA
Inherits System.Web.UI.Page
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private
Sub InitializeComponent()
End Sub
Protected
WithEvents lstJobs As System.Web.UI.WebControls.ListBox
Protected WithEvents cmdRunJob As System.Web.UI.WebControls.Button
Protected WithEvents lblStatus As System.Web.UI.WebControls.Label
Protected WithEvents lblPageTitle As System.Web.UI.WebControls.Label
Protected WithEvents txtSapSpoolNumber As System.Web.UI.WebControls.TextBox
'NOTE: The following placeholder declaration is required
by the Web Form Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code
editor.
InitializeComponent()
End Sub
#End Region
Public Declare
Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As
Long)
Dim SQLJobServer As SQLDMO.SQLServer2
Dim ServerName As String
Dim UserName As String
Dim Password As String
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
MyBase.Load
ServerName = ConfigurationSettings.AppSettings("ServerName").ToString
UserName = ConfigurationSettings.AppSettings("UserName").ToString
Password = ConfigurationSettings.AppSettings("Password").ToString
If
Not IsPostBack Then
Call ConnectSQLServer(ServerName, UserName, Password)
Call LoadTasks(SQLJobServer)
lstJobs.SelectedIndex = 0
Session("JobName") = lstJobs.SelectedItem.Text
lblStatus.Text = "Select a KPI Web Data Aquisition Job. Click the Run
button to launch the job. Wait for completion notification."
End If
End Sub
Private Sub ErrorHandler(ByVal strOutput As
String)
lblStatus.Text = strOutput.ToString
lblStatus.ForeColor = Color.Red
End Sub
Private Sub lstJobs_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstJobs.SelectedIndexChanged
Try
Dim lst As ListBox = sender
Session("JobName") = lst.SelectedItem.Text
If Session("JobName") = "KPIWeb_DA_Headcount_SAP" Then
txtSapSpoolNumber.Visible = True
Else
txtSapSpoolNumber.Visible = False
End If
Catch ex As Exception
Call ErrorHandler(ex.ToString)
End Try
End Sub
Private Sub cmdRunJob_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles cmdRunJob.Click
Try
Call RunSQLJob()
Catch ex As Exception
Call ErrorHandler(ex.ToString)
End Try
End Sub
Private Sub LoadTasks(ByVal SQLJobServer As SQLDMO.SQLServer)
Dim lTask As Object
For
Each lTask In SQLJobServer.JobServer.Jobs
If InStr(lTask.Name, "KPIWeb_DA", CompareMethod.Text) > 0 Then
lstJobs.Items.Add(lTask.Name)
End If
Next
End Sub
Sub ConnectSQLServer(ByVal ServerName, ByVal UserName,
ByVal Password)
Try
SQLJobServer = New SQLDMO.SQLServer
If UserName = "" Then
SQLJobServer.LoginSecure = True
SQLJobServer.Connect(ServerName)
Else
SQLJobServer.Connect(ServerName, UserName, Password)
End If
Catch ex As Exception
Call ErrorHandler(ex.ToString)
End Try
End Sub
Sub RunSAPJob()
Try
Dim Cmd As String
Dim Job As
New SQLDMO.Job
Dim JobStep As SQLDMO.JobStep
SQLJobServer.JobServer.Jobs.Item("KPIWeb_DA_Headcount_SAP").BeginAlter()
SQLJobServer.JobServer.Jobs.Item("KPIWeb_DA_Headcount_SAP").JobSteps.Remove(1)
SQLJobServer.JobServer.Jobs.Item("KPIWeb_DA_Headcount_SAP").DoAlter()
SQLJobServer.JobServer.Jobs.Item("KPIWeb_DA_Headcount_SAP").BeginAlter()
JobStep = New SQLDMO.JobStep
JobStep.StepID = 1
JobStep.Name = "Step1"
JobStep.DatabaseName = "KPI_Web"
JobStep.SubSystem = "TSQL"
Cmd = "declare @ret as integer EXEC @ret = master.dbo.xp_cmdshell
'D:\Server_Projects\Manufacturing\KPIWeb\DataAcquisition\Bin\NWNA_Util_ConsoleApp.exe
D:\Server_Projects\Manufacturing\KPIWeb\DataAcquisition\bin\NWNA_KPIWeb_HeadcountTempDollars.dll
Class1 Run 1' if @ret <> 0 RAISERROR ('Error in NWNA_Util_ConsoleApp',
16, 1) WITH SETERROR"
JobStep.Command = Cmd
JobStep.OnFailAction = SQLDMO.SQLDMO_JOBSTEPACTION_TYPE.SQLDMOJobStepAction_QuitWithFailure
JobStep.OnSuccessAction = SQLDMO.SQLDMO_JOBSTEPACTION_TYPE.SQLDMOJobStepAction_GotoNextStep
JobStep.OutputFileName = "D:\LogFiles\Manufacturing\KPIWeb\MOMO\KPIWeb_DA_Headcount_SAP.txt"
SQLJobServer.JobServer.Jobs.Item("KPIWeb_DA_Headcount_SAP").JobSteps.Add(JobStep)
SQLJobServer.JobServer.Jobs.Item("KPIWeb_DA_Headcount_SAP").StartStepID
= 1
SQLJobServer.JobServer.Jobs.Item("KPIWeb_DA_Headcount_SAP").DoAlter()
Catch ex As Exception
Call ErrorHandler(ex.ToString)
End Try
End Sub
Private Sub RunSQLJob()
Try
Dim JobServer As SQLDMO.JobServer
Dim Job As SQLDMO.Job
SQLJobServer = New SQLDMO.SQLServer
SQLJobServer.Connect(ServerName, UserName, Password)
JobServer = SQLJobServer.JobServer
Select
Case Session("JobName")
Case "KPIWeb_DA_Headcount_SAP"
Call RunSAPJob()
Case Else
End
Select
Job = JobServer.Jobs.Item(Session("JobName"))
Dim LastJobTime As
String = Job.LastRunTime.ToString
Dim SecondsCount As Integer
Dim StartTime As DateTime = Now
Dim FinisTime As DateTime
Job.Start()
Job.Refresh()
While (LastJobTime = Job.LastRunTime.ToString)
Sleep(1000)
Job.Refresh()
SecondsCount = SecondsCount + 1
End While
FinisTime = Now
Job.Refresh()
Dim oJobHisFltr As SQLDMO.JobHistoryFilter
Dim qr As SQLDMO.QueryResults2
Dim strResult As String
oJobHisFltr = SQLJobServer.JobServer.JobHistoryFilter()
oJobHisFltr.JobName = Session("JobName")
qr = SQLJobServer.JobServer.EnumJobHistory(oJobHisFltr)
strResult = "<U>Job Status</U>"
strResult = strResult & "<BR>Result Txt: " & qr.GetColumnString(1,
8) 'result text
strResult = strResult & "<BR>Result Int: " & qr.GetColumnString(1,
9) 'result Integer
strResult = strResult & "<BR>On Server: " & qr.GetColumnString(1,
17) 'server name
lblStatus.Text = strResult
Catch ex As Exception
Call ErrorHandler(ex.ToString)
End Try
End Sub
End Class
STILL NEED HELP?
If you are
still having problems, come to the #asp.net channel on DALnet.
Or, you can chat with RockydotNet now using InstantHelp.
