Vbscript To Run .sql File

How to Run VBScript from SQL Server 2008. SQL developers can run vb script files from SQL Server t-sql codes by using xpcmdshell procedure. It is sometimes a requirement especially for web developers to call vb script from SQL Server engine and execute vbscript from sql code. Of course calling vbscript from sql can be solved by using CLR in. No, I don't think you can just specify a file and then execute the sql scripts inside the file. You must open your file and store its content in a string var and pass it in a ExecuteNonQuery. To make it simplier you can do this: command1.ExecuteNonQuery(File.OpenText('C: Test.sql').ReadToEnd); cheers, Paul June A.

1. The cat Command. The following code demonstrates the first option for running an SQL script from.
2. I have written plenty of VBA in Access applications. For this, I am just trying to set up a SQL script as a VBS script, so the users don't have to go into SSMS to run it. They can just double-click the VBS script, specify the server and database when prompted, and the quick script will run for them.
3. 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.

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.

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:

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.

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

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

Run Sql File From 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?

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

About the author

Vbscript To Run Sql File