Vbscript To Run .sql File

15-09-2021

Vbscript To Run .sql File

Run Sql Script
How To Run .sql File
Vbscript To Run .sql File Processing
Vbscript To Run .sql File L File In Sqlplus Cmd

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?

Run Sql Script

Can I install File Sight v7.1.1.54 on SQL 2016? You give us an option to use SQLite or others database in File Sight. What the schema of the database? Powershell Execute Script Action. Using SQL server. How to Monitor SQL Server (Link is broke is your webpage) powershell action script not firing when tested against certain servers. The cat Command. The following code demonstrates the first option for running an SQL script from.

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.

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

Last Updated: 2008-07-18

How To Run .sql File

About the author