Oct 13, 2014 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. 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.

‘Make sure your database is online and ready to use

‘To create a DSN, just follow this link

The Script

Dim Connection, Recordset, SQL, Server, field, strAllFields

Vbscript To Run .sql File Processing

‘Declare the SQL statement that will query the database
SQL = “SELECT * FROM dbo.authors”

”’other sql statements
”’SQL = “SELECT DISTINCT * FROM dbo.authors ORDER BY au_lname DESC”

‘Create an instance of the ADO connection and recordset objects
Set Connection = CreateObject(“ADODB.Connection”)
Set Recordset = CreateObject(“ADODB.Recordset”)

‘Open the connection to the database
Connection.Open
“DSN=test;UID=ENTER_USERNAME;PWD=ENTER_PASSWORD;Database=pubs”

‘Open the recordset object executing the SQL statement and return records
Recordset.Open SQL,Connection

‘Determine whether there are any records
If Recordset.EOF Then
wscript.echo “There are no records to retrieve; Check that you have the correct SQL query.”
Else
‘if there are records then loop through the fields
Do While NOT Recordset.Eof

‘What I want to return
field = Recordset(“au_lname”)

”’return more than one column
”’field = Recordset(“au_lname”) & “, ” & Recordset(“au_fname”)

‘Store all returned values into a string
if field <> “” then
strAllFields = chr(13) & field + strAllFields
end if

Recordset.MoveNext
Loop
End If

‘Display all values
msgbox “Author Names ” & chr(13) & chr(13) & strAllFields

‘Close the connection and recordset objects to free up resources
Recordset.Close
Set Recordset=nothing
Connection.Close
Set Connection=nothing

For my reference

When working with Microsoft Access databases (or any other databases, for that matter) there are 4 types of SQL statements (or queries) that can be used to work with the information in the database, and these SQL statements are:

• select – obtain information from the database
• insert – add a new record into the database
• delete – remove records from the database
• update – change the details contained in the database

And all of these SQL statements can be used with VBScript – however, the first step is to connect to the database.
Connecting to a Microsoft Access Database
A programmer can easily use VBScript to make a connection to a Microsoft Access database by making use of one of Microsoft’s Active Data Objects:

dim connection_string : connection_string = _
“provider=microsoft.jet.oledb.4.0;” _
& “data source=c:customer.mdb”
dim conn : set conn = createobject(“adodb.connection”)
conn.open connection_string

In this example a connection is made to the database using the Microsoft Jet engine and accesses a Microsoft .mdb file. Then, with the connection in place, the programmer can then use VBScript to run queries and work with the contents of the database

The Select Query

The select query is perhaps the most commonly used query – this extracts information from the database and, in the case of VBScript, any results from the query are loaded into a recordset – in this example the contents of the id field from the name table are loaded into the recordset:

dim sql : sql = “select id from name”
dim rs : set rs = createobject(“adodb.recordset”)
rs.cursorlocation = 3 ‘Use a client-side cursor
rs.open sql, conn, 3, 3
‘A static copy of a set of records, locked only when updating

The contents of the recordset can then be used in VBScript – in this case to obtain a new id number for use in the database’s name table:

rs.movelast
dim id
if not rs.eof then
id = rs.Fields(0) + 1
else
id = 1
end if
msgbox id

This new id can be used when a new record is added to the table.

The Insert Statement
The next statement – the insert statement – adds a new record to a table:

dim surname : surname = “jones”
dim firstname : firstname = “john”
sql = _
“insert into name (id, surname,firstname)” _
& ” values (” & id & “,'” & surname & “‘,'” & firstname & “‘)”
conn.Execute sql

The insert statement is different from the select statement in that there is no result returned to VBScript – another select statement would have to be used in order to examine the new contents of the table.

The Delete Statement

The insert statement adds a record to a table and so, of course, the delete statement removes a record:
sql = _
“delete from name” _
& ” where surname = ‘” & surname & “‘” _
& ” and firstname = ‘” & firstname & “‘”
conn.Execute sql

As with the insert statement no result is returned.

The Update Query

The final statement – update – modifies a record in a database table, and so this next example inserts and then updates a record:
surname = “smith”
firstname = “jane”
sql = _
“insert into name (id, surname,firstname)” _
& ” values (” & id & “,'” & surname & “‘,'” & firstname & “‘)”
conn.Execute sql
dim new_surname : new_surname = “smyth”
sql = _
“update name” _
& ” set surname = ‘” & new_surname & “‘” _
& ” where surname = ‘” & surname & “‘” _
& ” and firstname = ‘” & firstname & “‘”
conn.Execute sql

And again no result would be returned and a select statement would be needed for the updated contents to be viewed.

Summary

The 4 types of SQL statements (or queries) used with databases, such as Microsoft Access, are:

• select
• insert
• delete
• update

Each can be used with VBScript by making use of Microsoft’s Active Data Objects which make data manipulation very easy:

• use the ADO to connect to the database
• use the ADO to create a recordset from the select statement.
• use the ADO to change the contents of the database using insert, delete and update statements

Giving the VBScript programmer full control of any database.

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

Vbscript To Run .sql File

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?

Execute Sql Script File

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.

Vbs Run Sql File

Vbscript To Run .sql File Converter

Vbscript To Run .sql File L File In Psql

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