Oct 28, 2001 Execute VBScript commands or.vbs files via T-SQL. Cade Bryant, 2003-11-05 spExecVBScript allows you to execute either a.vbs file or an ad-hoc VBScript command within a T-SQL batch.

• Using the command line we can run or execute any SQL scripts file with SQL Server Management Studio(SSMS) step to step Step 1: Please below my Example, copy the following code and and paste into the notepad it as save Product.sql file under the C:SQLScripts folder.
• Apr 10, 2011 sql server comand line execute an.sql fike How to run the next line in sql when the previous line is executed in a.sql file Execution of bunch of create tables and stored proedure with.bat file.

By: Armando Prato | Updated: 2008-07-18 | Comments (24) | Related: 1 | 2 | 3 | More >Database Administration

Problem

My company has a number of SQL installation scripts that build a fresh copy of our database. Each script relates to a specific SQL task. One script builds the database, another builds the tables, etc. I call SQLCMD repeatedly to execute each script. Is it possible to concatenate these scripts together and execute them with a single call to SQLCMD?

Solution

Yes it is. SQLCMD offers the :r command. When :r is encountered in a SQL script, it essentially tells the SQLCMD utility to include the file referenced into the calling script. This can afford you the opportunity to break up a single script into multiple scripts that represent functional areas of the database or to replace multiple SQLCMD calls with a single call to a main SQL file. Furthermore, using the :r command to execute multiple scripts in a single batch allows you define a single set of variables which can be seen by all the included scripts (provided there is no intervening GO terminator). SQLCMD was introduced in SQL Server 2005 and is the replacement for osql which Microsoft will be deprecating in a future release. If you're not familiar with SQLCMD, it is a command line utility that can be used to execute T-SQL commands and scripts from the operating system.

NOTE: You can download the sample files via the attached .zip file which you can unzip and use to execute the sample code that will be presented in this tip.

Run Sql File In Psql

In the forthcoming example, I'll create 5 .sql files representing functional areas of a sample database called MSSQLTIPS. The first script is called CREATE_DB.sql and creates a database on your SQL 2005 Server called MSSQLTIPS. This script then includes 4 scripts (using the :r command) to perform table creation, table inserts, index creation, and stored procedure creation. A .bat file is created to execute SQLCMD from the Windows operating system in order to create a fresh database.

I first create a sub-folder under my C: drive called C:Scripts. I store the following SQL scripts in this folder.

Script code to create tables

Script code to insert data

Script code to create indexes

Script code to create procedures

Script code to create the new database and objects

In the root C: folder, I create a file called create_db.bat which I use to create the database with all objects

Double clicking the .bat file, I see that each script processed and that the database created successfully.

The SQLCMD utility offers a wealth of commands that can used to modify the .bat file to further control and refine output and error behavior.

Next Steps

• Read more about the wealth of other SQLCMD commands in the SQL Server 2005 Books Online
• Read this SQLCMD tutorial in the SQL Server 2005 Books Online
• If you're using osql in your scripts under SQL Server 2005, consider replacing osql references with SQLCMD

About the author

Usually, when we run SQL scripts, it is done on an ad hoc basis. We usually run one file at a time just once. From time to time there is the need to run a set of SQL script files. One common reason I’ve seen for doing this is to rebuild a database for testing. If you had all of your create tables/stored procedure commands stored in a series of SQL script files, then you could rebuild your database fairly easily by executing the SQL files in sequence.

SQL Server provides a tool to do this called SQLCMD.

The sqlcmd utility lets you run entire SQL script files full of t-sql via command prompt. Leveraging this tool, you can create a batch file to run the sql script files in sequence.

How To Execute A SQL Script File Via Command Prompt With SQLCMD

Although there are TONS of command line options for executing a sql script. Just executing a sql script file with the basic options is very easy.

The above command uses the -i option to specify the input file of DropTables.sql. Everything inside this file will be execute on the current database.

Vbscript To Run .sql File Repair

NOTE: When you create your SQL script files, I always recommend putting a USE statement at the top of your script. This will ensure that you are using the correct database when your dropping and creating tables.

Run A Batch Of SQL Script Files

Now that you know how to run one SQL script file… running multiple should be easy. All you need to do is create a batch file and run one script after the other.

Your batch file will look something like this:

Vbscript To Run .sql File L File For Postgres In Dbeaver

What About Errors?

Vbscript To Run .sql File L File In Sqlplus Cmd

So what happens if the SQL script file throws an error? The above commands I showed you will just ignore any errors. If you want to capture your errors, you need to add the command line option /b. This flag will tell the sqlcmd utility to stop processing the script and output a DOS ERRORLEVEL value. You can then check the DOS ERRORLEVEL value after each execution.

Vbscript To Run .sql File Processing

Reference: http://msdn.microsoft.com/en-us/library/ms162773.aspx