SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Execute SQL job through batch file

By Divya Agrawal,

Every database person might have came 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 scheduled set. But in the case where the job has to be executed "On demand" then it needs human interference and the person should obviously be a technical person.

This article shows a good way of executing such maintenance tasks by any user regardless of whether the person is a technical or not. Let's say I have an SQL job "BACKUPTEST" that does the work of backing up a database. I wanted to execute that job "On Demand". Whenever anyone wants to run the backup, they should not necessarily have knowledge of SQL Server. I wanted to make a batch file out of that, so that any user can execute the job easily with a single click.

There are various solutions of the problem stated above. One is write a VBS script and using DMO the job can be executed. The one that I have found to be a better solution is to execute the osql command in a batch file. The osql utility allows you to enter Transact-SQL statements, system procedures, and script files. This utility uses ODBC to communicate with the server.

The first step would be try executing the job through command prompt using 'osql'. Open the command prompt and execute the following osql command in it:

osql -S "SERVER INSTANCE" -E -Q"exec msdb.dbo.sp_start_job ' BACKUPTEST ' "

Here "SERVER INSTANCE" is the name of the SQL Server instance running on your machine and on which the job has to be executed. "BACKUPTEST" is the name of the job which will do the desired maintenance task. After executing the command above you will notice that the system stored procedure 'sp_start_job' executes the desired job on the desired server instance.

The next step is to make a batch file. Open a notepad and type the commands as:

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.

Total article views: 15909 | Views in the last 30 days: 3
Related Articles

Replication issue Error executing a batch of commands. Retrying individual commands.

My Tran. Replication is working on other server, but when I crete new Subscription on new server I a...


How to execute an SSIS package from the command line or a batch file

An SSIS package that is executed on an ad hoc basis can be run from Business Intelligence Developmen...


Miant Plan -batch file execution

Miant Plan -batch file execution


Batch SSIS pkg execution from Business Intelligence Development Studio

The Execute Package Task and a second instance of Business Intelligence Development Studio can be a ...

agent jobs    
batch file