Different ways to execute a SQL Agent job
April 16th 2009 07:42
Different ways to execute a SQL Agent job
Problem:
Every database person might have come across the situation of maintenance tasks such as backing up of databases, re-indexing tables and other such tasks. We often schedule jobs for such tasks, so that they execute as per the set schedule. But there is sometimes the need for these tasks to be executed “On Demand”. This tip shows various ways of executing such tasks on demand by any user regardless of whether the person is technical or not.
Solution
Let’s say I have a job called “BACKUPTEST” which backups the test databases. I want to be able to execute the job “On Demand”, so whenever anyone needs to do the backup this can be done. In this article I will show you how you can execute such Jobs easily through various ways.
In this tip we will look at these four methods:
SQL Server Management Studio
T-SQL commands
DMO (Distributed Management Objects)
OSQL
Also, this tip assumes that the jobs have already been setup.
----------------------------- ----------------------------- ----------------------
(1) - SQL Server Management Studio
The first way that most people are probably aware of is to use SQL Server Management Studio.
SQL Server Agent is the job scheduling tool for SQL Server.
To execute a job on demand using the GUI, open the SQL Server Agent tree, expand Jobs, select the job you want to run, right click on that job and click ‘Start Job’ and the job will execute.
----------------------------- ----------------------------- ----------------------
(2) -T-SQL commands
The second way is through a T-SQL statement using the 'sp_start_job' stored procedure which instructs SQL Server Agent to execute a job immediately. It is a stored procedure in the 'msdb' database.
The syntax for the sp_start_job stored procedure is:
sp_start_job
[@job_name] or [@job_id ]
[,@error_flag ]
[,@server_name]
[,@step_name ]
[,@output_flag ]
Arguments:
[@job_name] | [@job_id ] Is the name of the job to start. Either job_id or job_name must be specified, but both cannot be specified. job_name is sysname, with a default of NULL.
[@error_flag =] error_flag Reserved.
[@server_name =] 'server_name' Is the target server on which to start the job. server_name is nvarchar(30), with a default of NULL. server_name must be one of the target servers to which the job is currently targeted.
[@step_name =] 'step_name' Is the name of the step at which to begin execution of the job. Applies only to local jobs. step_name is sysname, with a default of NULL
[@output_flag =] output_flag Reserved.
When a job run it will have one of two return codes:
0 (success)
1 (failure)
To run the job ‘BACKUPTEST’ it can be executed by a single T-SQL statement: such as:
EXEC msdb.dbo.sp_start_job 'BACKUPTEST'
----------------------------- ----------------------------- ----------------------
(3) -DMO (Distributed Management Objects)
Another way of executing the job is through a VBS script using Distributed Management Objects (DMO).
Here is the basic script syntax.
On Error Goto 0: Main()
Sub Main()
Set objSQL = CreateObject("SQLDMO.SQLServe r")
' Leave as trusted connection
objSQL.LoginSecure = True
' Change to match the name of your SQL server
objSQL.Connect "Enter Server Name Here"
Set objJob = objSQL.JobServer
For each job in objJob.Jobs
if instr(1,job.Name,"Enter Job Name Here") > 0 then
msgbox job.Name
job.Start
msgbox "Job Started"
end if
Next
End Sub
Here is sample executing the "BACKUPTEST" job on server "SQLTEST1". This uses NT authentication to run this script.
On Error Goto 0: Main()
Sub Main()
Set objSQL = CreateObject("SQLDMO.SQLServe r")
' Leave as trusted connection
objSQL.LoginSecure = True
' Change to match the name of your SQL server
objSQL.Connect "SQLTEST1"
Set objJob = objSQL.JobServer
For each job in objJob.Jobs
if instr(1,job.Name,"BACKUPTEST") > 0 then
msgbox job.Name
job.Start
msgbox "Job Started"
end if
Next
End Sub
This code would then be saved in a file and named something like "RunJob.vbs". You can then double click on the file to execute it or run the code from a command line.
----------------------------- ----------------------------- ----------------------
(4) - Using osql utility
Lastly, we can start the job using osql commands.
The osql utility allows you to enter T-SQL statements, system procedures, and script files.
Here is the basic script syntax.
osql -S "Enter Server Name Here" -E -Q"exec msdb.dbo.sp_start_job 'Enter Job Name Here'"
Open a command prompt and execute the below osql command in it:, replacing your server name and job name.
osql -S "SQLTEST1" -E -Q"exec msdb.dbo.sp_start_job 'BACKUPTEST'"
The next step is to make a batch file which can be run over and over again.
Open notepad and type the commands as follow:
ECHO Executing job
ECHO.
pause
osql -s "MYPC\SQL" -E -Q"exec msdb.dbo.sp_start_job 'BACKUPTEST' "
ECHO job execution completed
pause
CLS
EXIT
Save the file as “job.bat”.
The batch is now ready for use. Just double click on it and it will do the maintenance work without having any knowledge of SQL Server.
----------------------------- ----------------------------- ----------------------
Permissions
You might have noticed in all the four solutions the msdb stored procedure ‘sp_start_job’ is used in one way or another.
By default, members of the sysadmin fixed server role can execute this stored procedure.
Other users must be granted one of the following SQL Server Agent fixed database roles in the msdb database:
SQLAgentUserRole
SQLAgentReaderRole
SQLAgentOperatorRole
Members of SQLAgentUserRole and SQLAgentReaderRole can only start jobs that they own.
Members of SQLAgentOperatorRole can start all local jobs including those that are owned by other users.
Members of sysadmin can start all local and multiserver jobs.
For details about the permissions of these roles, see SQL Server Agent Fixed Database Roles. and SQL Server Agent Fixed Database Roles
Problem:
Every database person might have come across the situation of maintenance tasks such as backing up of databases, re-indexing tables and other such tasks. We often schedule jobs for such tasks, so that they execute as per the set schedule. But there is sometimes the need for these tasks to be executed “On Demand”. This tip shows various ways of executing such tasks on demand by any user regardless of whether the person is technical or not.
Solution
Let’s say I have a job called “BACKUPTEST” which backups the test databases. I want to be able to execute the job “On Demand”, so whenever anyone needs to do the backup this can be done. In this article I will show you how you can execute such Jobs easily through various ways.
In this tip we will look at these four methods:
SQL Server Management Studio
T-SQL commands
DMO (Distributed Management Objects)
OSQL
Also, this tip assumes that the jobs have already been setup.
----------------------------- ----------------------------- ----------------------
(1) - SQL Server Management Studio
The first way that most people are probably aware of is to use SQL Server Management Studio.
SQL Server Agent is the job scheduling tool for SQL Server.
To execute a job on demand using the GUI, open the SQL Server Agent tree, expand Jobs, select the job you want to run, right click on that job and click ‘Start Job’ and the job will execute.
----------------------------- ----------------------------- ----------------------
(2) -T-SQL commands
The second way is through a T-SQL statement using the 'sp_start_job' stored procedure which instructs SQL Server Agent to execute a job immediately. It is a stored procedure in the 'msdb' database.
The syntax for the sp_start_job stored procedure is:
sp_start_job
[@job_name] or [@job_id ]
[,@error_flag ]
[,@server_name]
[,@step_name ]
[,@output_flag ]
Arguments:
[@job_name] | [@job_id ] Is the name of the job to start. Either job_id or job_name must be specified, but both cannot be specified. job_name is sysname, with a default of NULL.
[@error_flag =] error_flag Reserved.
[@server_name =] 'server_name' Is the target server on which to start the job. server_name is nvarchar(30), with a default of NULL. server_name must be one of the target servers to which the job is currently targeted.
[@step_name =] 'step_name' Is the name of the step at which to begin execution of the job. Applies only to local jobs. step_name is sysname, with a default of NULL
[@output_flag =] output_flag Reserved.
When a job run it will have one of two return codes:
0 (success)
1 (failure)
To run the job ‘BACKUPTEST’ it can be executed by a single T-SQL statement: such as:
EXEC msdb.dbo.sp_start_job 'BACKUPTEST'
----------------------------- ----------------------------- ----------------------
(3) -DMO (Distributed Management Objects)
Another way of executing the job is through a VBS script using Distributed Management Objects (DMO).
Here is the basic script syntax.
On Error Goto 0: Main()
Sub Main()
Set objSQL = CreateObject("SQLDMO.SQLServe r")
' Leave as trusted connection
objSQL.LoginSecure = True
' Change to match the name of your SQL server
objSQL.Connect "Enter Server Name Here"
Set objJob = objSQL.JobServer
For each job in objJob.Jobs
if instr(1,job.Name,"Enter Job Name Here") > 0 then
msgbox job.Name
job.Start
msgbox "Job Started"
end if
Next
End Sub
Here is sample executing the "BACKUPTEST" job on server "SQLTEST1". This uses NT authentication to run this script.
On Error Goto 0: Main()
Sub Main()
Set objSQL = CreateObject("SQLDMO.SQLServe r")
' Leave as trusted connection
objSQL.LoginSecure = True
' Change to match the name of your SQL server
objSQL.Connect "SQLTEST1"
Set objJob = objSQL.JobServer
For each job in objJob.Jobs
if instr(1,job.Name,"BACKUPTEST") > 0 then
msgbox job.Name
job.Start
msgbox "Job Started"
end if
Next
End Sub
This code would then be saved in a file and named something like "RunJob.vbs". You can then double click on the file to execute it or run the code from a command line.
----------------------------- ----------------------------- ----------------------
(4) - Using osql utility
Lastly, we can start the job using osql commands.
The osql utility allows you to enter T-SQL statements, system procedures, and script files.
Here is the basic script syntax.
osql -S "Enter Server Name Here" -E -Q"exec msdb.dbo.sp_start_job 'Enter Job Name Here'"
Open a command prompt and execute the below osql command in it:, replacing your server name and job name.
osql -S "SQLTEST1" -E -Q"exec msdb.dbo.sp_start_job 'BACKUPTEST'"
The next step is to make a batch file which can be run over and over again.
Open notepad and type the commands as follow:
ECHO Executing job
ECHO.
pause
osql -s "MYPC\SQL" -E -Q"exec msdb.dbo.sp_start_job 'BACKUPTEST' "
ECHO job execution completed
pause
CLS
EXIT
Save the file as “job.bat”.
The batch is now ready for use. Just double click on it and it will do the maintenance work without having any knowledge of SQL Server.
----------------------------- ----------------------------- ----------------------
Permissions
You might have noticed in all the four solutions the msdb stored procedure ‘sp_start_job’ is used in one way or another.
By default, members of the sysadmin fixed server role can execute this stored procedure.
Other users must be granted one of the following SQL Server Agent fixed database roles in the msdb database:
SQLAgentUserRole
SQLAgentReaderRole
SQLAgentOperatorRole
Members of SQLAgentUserRole and SQLAgentReaderRole can only start jobs that they own.
Members of SQLAgentOperatorRole can start all local jobs including those that are owned by other users.
Members of sysadmin can start all local and multiserver jobs.
For details about the permissions of these roles, see SQL Server Agent Fixed Database Roles. and SQL Server Agent Fixed Database Roles
| 24 |
| Vote |
Add Comments
Read More
