How can we automate stored procedures using batch files

Automating SQL stored procedures by using batch files.

Before we can automate stored procedures that are house in the Geobank database we must first understand a little bit about 3 individual tools that can be used for this automation.

The first tool we need to understand a bit is batch files.

A Batch file is a simple computer file, that carries within it a list of instructions that a computer can follow to perform tasks.

A simple example would be to ask the computer to display the words “hello there”.

First we will need to create a new text file.

Right click in any folder or on the desktop and select the create new text file option.

Once there, this new file should be renamed to a file name that makes logical sense and we need to change the .txt extension at the back to .bat.

This file cannot be run, as it does not contain any instructions at this time.

Right click on the file and select the edit option.

Please type into this file exactly the lines as seen below and then save the file.

Once saved, please close the editor and then double click your .bat file to run it.

You should see a console window that looks like this:

Now that we know how to make a basic batch file, the second tool we need to understand a little bit is the SQLCMD tool.

SQLCMD is a command-line utility for ad hoc, interactive execution of SQL statements and scripts and for automating SQL scripting tasks. This will enable us to call the stored procedures from outside the SQL Server environment and execute them

For now, this information should be enough to continue. 

For interest sake here is a list of SQLCMD commands that can be used.

For our purposes, we will be using a small number of commands.

To construct a basic version of the batch file, please create a new batch file and then insert the steps into that batch file as shown below.

As you will see this batch file will contain a line of code that "activates" the SQLCMD tool for us to use.

The last line we are connecting to our database and calling our stored procedure using SQLCMD.

I have created a basic stored procedure that will simply create a new table with a few columns.

To test this we ensure the database does not contain the table, then run the batch file and check afterwards.

Our initial check confirms that there is no table called BAT-table in the database.

We then run the batch file that we created, the table should be created in the database from outside SQL Server. When we rerun the query, we can see the table as now been created.

So this is a very basic example of how we can use batch files to call stored procedures in our database from outside of the SQL Server environment.

The final step would be to use the Task Scheduling tool to run the batch file for us.

Open the task scheduling tool, that comes with Windows and click on the link to create a new basic task.

Provide the task with a descriptive name and a description if you require one.

Click on the next button.

We can now decide how often this task should be run. 

Perhaps we can run this task daily at the end of the work day.

This is very use full when large amounts of data might have to be backed up.

Simply decide how often this task has to be run and a time when it will be called by Windows automatically.

Under action, please select start a program option.

Browse to and select the .BAT file that you have created and click next.

On the last screen you can simply click on the finish button.

There you have it, you have now created an automated process that will call any stored procedure daily from outside of SQL Server.

Want to learn more?

Online Help Manuals - Click here for the latest version

Learning Management System - Click here to login or here to request access