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

SQLCMD and Batch File magic

By Manu Mohanan, (first published: 2015/11/19)

Preface

Have you ever wanted a simple and effective tool that can execute a bunch of scripts from a folder? If you have, and did not want to use 3rd party tools for getting the job done, you have come to the right place.

First of all, I would Thank Jason Selburg for his method of executing the scripts which made me put this Contribution as one more method of simplifying your daily task. You can see his article on SQL CMD Mode for more information.

Problem Statement

As part of a development team, your daily work includes fixing bugs and adding new changes to SQL scripts. You also need to check that code into your Version Control System (VCS). That means every time another developer changes any script, you would need to execute the same script on your database so that it is up-to date. You need to do this at least every day, if not every couple of hours.

So once you have the latest script, you can use this cool trick to update multiple databases in one go. You will need two folders with scripts. For my illustration, I have two folders called:

  1. Create Scripts
  2. Change Scripts

You can see this in the image below.

Each folder has its own set of scripts:

Now the Magic

Open your favorite text Editor or Notepad, if you prefer, and enter the following:

@@echo off

del errors /f /s /q

rd Errors

md Errors

FOR %%A IN (*.SQL) DO ( sqlcmd -S SERVERNAME -d DATABASE1 -U username -P password -i "%%A" -o "Errors\%%AError_DB1.txt" -I )

FOR %%A IN (*.SQL) DO ( sqlcmd -S SERVERNAME -d DATABASE2 -U username -P password -i "%%A" -o "Errors\%%AError_DB2.txt" -I )

You would need to change the following as per your Database Name(s) and credentials in the above script:

  • SERVERNAME – Your Database Server Name.
  • DATABASE1 – Your Database name.
  • Username – Your SQL Username.
  • Password – Your SQL Password.

Save this file with the Extension as .bat in one of your scripts folder.

Copy this batch file to the other script folder.

The Explanation

Here is an explanation of each section of the code.

del errors /f /s /q

When executing the scripts, we dump all errors to a folder called Errors. This needs to be cleaned up. This will delete the contents of folder silently.

rd Errors

This will delete the folder Errors.

md Errors

This will create a new folder names Errors. I know it is redundant, but why not start with a clean slate.

FOR %%A IN (*.SQL) DO ( sqlcmd -S MANU -d DATABASE1 -U username -P password -i "%%A" -o "Errors\%%AError_DB1.txt" -I )

Using a FOR loop we select each file name in sequential order as per the name of the file which ends with .sql extension in the current folder and pass the file name to execute with Server Name, Username, Password and the database name using SQLCMD.

%%A

here denotes the name of the current file name in the loop.

–o

outputs any errors/messages from the script to a file in the errors folder followed by the Database name.

Adding the database name can help you differentiate the error files when you add multiple FOR Loops for multiple databases. You can have n number of loops to n number of databases as in the example. I have 6 Databases that updates in one go.

Ready, Set, Run…

Now all you have to do is double click the batch file in the respective folders to execute all the scripts.

On executing the batch file, a new folder called Errors will be created which generated output from each script that was executed against the Server.

It would contain something like these.

If you see there are 2 files for CreateScript – 1.sql. Each error file pertains to different database.

The only caveat with this method is if there are no errors, it will still create the Error File. You can use the size of the file to get around this.

https://msdn.microsoft.com/en-us/library/ms162773.aspx is the command line reference to SQLCMD if you want to add more to this.

 
Total article views: 8322 | Views in the last 30 days: 22
 
Related Articles
FORUM

URGENT: Error executing scripts......

URGENT: Error executing scripts......

FORUM

Script error when trying to Move a Report from Folder to Folder via SSRS2008

I am getting a "object expected" error when I try and move a report from Folder to another folder

ARTICLE

Executing Multiple Scripts in a Folder using the ScriptRunner Utility

Ever have a large batch of scripts you need to run? It takes a while if you have to open each one in...

FORUM

Error....While creating Folder through SQL

Error....While creating Folder through SQL

FORUM

Script to create datestamp folder causing issue

Script to create datestamp folder causing issue

Tags
command line    
scripts    
sqlcmd    
 
Contribute