I'm using sp_start_job to run a job from a stored procedure. This is fine but I want to know the result ie. success or failure. I'm not having much luck with @output_flag. I can't get it to return anything.
I need to give permissions for an user to start a job on the SQL server using msdb.dbo.sp_start_job stored procedure,what kind of permissions she will need on the server? Any help is appreciated.
I'm not able to find sp_start_job under system stored procedures in msdb. If I just run sp_start_job I get execute permission denied. If I run sp_helptext sp_start_job I get the below error
The object 'sp_start_job' does not exist in database 'msdb' or is invalid for this operation
I am not sure, what is missing, permission or the system procedure itself is not there?
I would like to use sp_start_job to execute a SSIS Package but I am not sure how it works. If there are two requests run the job two times simultaneously or sequentially?
I have a sql 7 scheduled job that has several steps. Each step uses the TSQL msdb..sp_start_job procedure to start a job.
How can I make each step wait for the previous step to complete before starting? I know the sp_start_job does its job and starts a job, but I want to know when the job it started finishes!
Post and email your reply please (firstname.lastname@example.org)
I need to disable and move orphaned computer objects in my Active Directory. The SQL Agent has permission to do this. I have created a stored procedure for the task with intentions of executing it with sp_start_job. However, I cannot execute it in SQL 2005. How can I grant permission to this (login) to execute sp_start_job? This is all run from a web page and NOT the Query Window.
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
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?
ALTER procedure [dbo].[sp_MyProc]
EXEC msdb.dbo.sp_start_job @job_name = 'MyJob'
I'm trying to write a procedure that calls a job. If I execute it (calling it from adp) as a user that is not db_owner (I guess), I get the error: The Execute permission was denied on the object sp_start_job, database 'msdb', schema 'dbo'.
How can I resolve this problem?
Hi guys, please see if you can help me with this...
I have an activation stored procedure that starts a SQL Agent Job which executes a SSIS package. However when the stored procedure runs it fails with the error EXECUTE permission denied on object 'sp_start_job'. The message queue was created under the €˜sa€™ account, and I have tried setting the activation procedure to run as SELF, OWNER as well as creating a user account with sysadmin rights and running it under that account, all with the same result. When I run the stored procedure manually (under pretty much any of the accounts I have set up) it executes without any errors and kicks off the job it is meant to. The error only occurs when the stored procedure is activated via the service broker message queue.
I changed the stored proc to write out system_user and current_user to a table so that I could see what it was running as and as it turns out it appears to be running as the correct user (which is €˜sa€™ when set to SELF) but not inheriting the correct permissions.
Is this a bug, and if so is there some work-around for it?