Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: 14627 | Views in the last 30 days: 47
 
Related Articles
FORUM

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...

FORUM

Miant Plan -batch file execution

Miant Plan -batch file execution

FORUM
FORUM

Executing batches asychronously?

discuss support for asynchronous batch execution in sql server 2000

FORUM

Try to get in to SQL Server instance in single user mode through command prompt

Try to get in to SQL Server instance in single user mode through command prompt

Tags
batch file    
jobs    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones