VBScript does not 'execute' files. This would be bad. Any website you visited could then potentially 'execute' any file on your computer. In VB you can use the Shell command. I use.SQL files as well, which are pretty much query files to our SQL Server. What are you tryign to accomplish here? Sep 28, 2020 In this tech-recipe post, we will review and learn to execute SQL files using SQLCMD. Moreover, saving the output log of executed SQL files for future references. Save the file with a T-SQL statement as a.SQL file. Make sure it contains a GO keyboard at the end of the script file to indicate the end of the batch statement. 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.

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. Smaart 7 keygen osx. Everything inside this file will be execute on the current database.

Sql Server Run Sql File

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:

2 4	SQLCMD/i'c:scriptsCreateTables.sql' PAUSE

What About Errors?

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.

IF%ERRORLEVEL%>(PAUSE)

SQLCMD/i'c:scriptsCreateTables.sql'/b

IF%ERRORLEVEL%>0(PAUSE)

Backyard baseball 2005 download mac.
Reference: http://msdn.microsoft.com/en-us/library/ms162773.aspx

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.

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

Vbscript To Run .sql File Extension

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

Vbscript To Run .sql Files

About the author

Armando Prato has close to 30 years of industry experience and has been working with SQL Server since version 6.5.
View all my tips

Related Resources

Vbscript To Run .sql File