Sp_start_job Does Not Evaluate "@server_name" Parameter
We want to start a job remotely on another SQL7 server.
innocently we thought that should be no problem with sp_start_job.
We used the command listed below:
EXEC sp_start_job @job_name='MyJobName', @server_name='blablabla'
The strange thing is, that sp_start_job ignores the @server_name at all.
Even if we enter complete nonsense for @server_name the job gets started as long it is present on the local sqlserver instance.
sp_start_job seems to search locally for the given job_name, only.
Consequently if we enter the remote server's name the job fails as the
jobname is not present on the local sqlserver.
Any ideas what's going wrong here.
Greetings from Mannheim, Germany
View Replies !
Monitor SSIS Job Started With Sp_start_job From .NET App
The following article has a good code sample for starting a SSIS job using SQLCommand from within a .NET application:
This runs a non-local job.
1. - it starts the job, and returns a result code (success or failure), but does not monitor the job and report when it completes. This is supposedly the task of sp_help_job; however, I have been unable to get this sp to return the execution_status value - it comes back with '0' even when the job is idle (4) or executing (1). Consequently a timed loop calling this sp never ends, as 0 means 'not idle or disabled'.
Here is the code I used to return the execution_status parameter:
Dim jobConnection As SqlConnection
Dim jobCommand As SqlCommand
Dim jobReturnValue As SqlParameter
Dim jobParameter As SqlParameter
Dim jobResult As Integer = 0
Dim stJob as String = "Test Job"
jobConnection = New SqlConnection(My.Settings.connMaster)
jobCommand = New SqlCommand("sp_help_job", jobConnection)
jobCommand.CommandType = CommandType.StoredProcedure
do while jobResult = 0
jobParameter = New SqlParameter("@job_name", SqlDbType.VarChar)
jobParameter.Direction = ParameterDirection.Input
jobParameter.Value = stJob
jobReturnValue = New SqlParameter("@execution_status", SqlDbType.Int)
jobReturnValue.Direction = ParameterDirection.ReturnValue
jobResult = jobCommand.Parameters("@execution_status").Value
The second problem is that if more than one instance of the job is running, how can the sp_help_job determine which instance it is supposed to monitor? You would also need the session ID as well as the Job Name. So how do you get this from sp_start_job, and how do you pass it to sp_help_job?
Right now I am getting by with getting a timestamp from the SQL Agent right before running the sp_start_job, and using this and the job name against the run_requested_date in dbo.sysjobactivity to determine when the stop_execution_date ceases to be NULL (which suggests, but does not confirm, that the job finished):
Dim tmStamp As DateTime
tmStamp = GetTimeStamp(My.Settings.connSysDB)
Dim strSQL As String = " select * from dbo.sysjobactivity sjh inner join sysjobs sj on sj.job_id = sjh.job_id" & _
" where sj.name = '" & stJob & "' and sjh.run_requested_date >= '" & tmStamp & "' and sjh.stop_execution_date IS NULL"
jobResult = GetCount(My.Settings.connSysDB, strSQL)
Public Function GetTimeStamp(ByVal strConnection As String) As DateTime
Dim x As DateTime
Dim Conn As ADODB.Connection
Dim RS As ADODB.Recordset
RS = New ADODB.Recordset
Conn = New ADODB.Connection
Conn.ConnectionString = strConnection
RS.ActiveConnection = Conn
RS.Open("SELECT CURRENT_TIMESTAMP as 'timestamp'", Conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly)
If RS.RecordCount > 0 Then
x = RS.Fields(0).Value
This is not a great solution because it does not know what instance, if there is more than one, of the job it is reporting on.
What is a better way of finding out when a specific instance of a specific unscheduled job is finished running?
View Replies !