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. 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. Jul 11, 2014 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.
By: Armando Prato | Updated: 2008-07-18 | Comments (24) | Related: 1 | 2 | 3 | More >Database Administration
- 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.
- 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.
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
Last Updated: 2008-07-18
About the author
View all my tips
‘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
‘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:
Vbscript Execute Sql File
• 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
Run Sql Script
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
Execute Sql Script File
Giving the VBScript programmer full control of any database.