Connect to SQL Server with VBScript: A Step-by-Step Guide

In this guide, we’ll explore how to connect to SQL Server with VBScript, execute a query, and retrieve the results. This can be particularly useful for system administrators or developers who want a lightweight way to interact with SQL Server without needing to set up a full-fledged application.

Prerequisites

Before we get started, ensure the following:

• You have access to a SQL Server instance.
• The SQL Server instance is configured to allow remote connections.
• You have valid credentials (username and password) with permissions to run queries on the SQL Server.

Setting Up Your Environment

VBScript is natively supported in Windows, so no additional installations are necessary. For this script, you’ll need access to the ADODB library, which is part of the Windows OS and can be used to establish connections with databases.

Step 1: Basic Structure of a VBScript File

Create a new text file and save it with a .vbs extension. For example, RunSQLQuery.vbs. Open this file with a text editor and start by defining your connection parameters and the basic structure of the script.

Step 2: Set Up the Connection to SQL Server

To connect to a SQL Server database, we’ll use ADODB.Connection in VBScript. Here’s how to set it up.

'how to connect to SQL Server with VBScript
Dim conn
Set conn = CreateObject("ADODB.Connection")

Dim connectionString
connectionString = "Provider=SQLOLEDB;Data Source=SERVER_NAME;Initial Catalog=DATABASE_NAME;User ID=YOUR_USERNAME;Password=YOUR_PASSWORD;"

' Open the connection
conn.Open connectionString

In the above code:

• Replace SERVER_NAME with the name or IP address of your SQL Server.
• Replace DATABASE_NAME with the name of the database you want to connect to.
• Replace YOUR_USERNAME and YOUR_PASSWORD with your SQL Server credentials.

Note: If your SQL Server uses Windows authentication, you can modify the connection string as follows:

'how to connect to SQL Server with VBScript
connectionString = "Provider=SQLOLEDB;Data Source=SERVER_NAME;Integrated Security=SSPI;Initial Catalog=DATABASE_NAME;"

Step 3: Write and Execute an SQL Query

Once the connection is established, you can write an SQL query and execute it. For this, we’ll use ADODB.Recordset to retrieve query results.

Here’s an example of executing a SELECT query to retrieve data from a table:

'how to connect to SQL Server with VBScript
Dim rs
Set rs = CreateObject("ADODB.Recordset")

Dim query
query = "SELECT * FROM TableName"

' Execute the query
rs.Open query, conn

' Check if records were returned
If Not rs.EOF Then
    ' Loop through the records
    Do Until rs.EOF
        WScript.Echo "Column1: " & rs("Column1") & ", Column2: " & rs("Column2")
        rs.MoveNext
    Loop
Else
    WScript.Echo "No records found."
End If

' Close the recordset
rs.Close
Set rs = Nothing

In the code above:

• Replace TableName with the name of the table you want to query.
• Adjust Column1 and Column2 to match the column names in your table.

The script uses rs(“ColumnName”) to access each column’s value in the current row of the Recordset. rs.MoveNext moves to the next row until all rows are processed.

Step 4: Handling Errors

It’s important to add error handling to make your script robust. We can use On Error Resume Next to capture any issues during execution and provide meaningful feedback.

Here’s how to add error handling:

'how to connect to SQL Server with VBScript

On Error Resume Next

Dim conn
Set conn = CreateObject("ADODB.Connection")

Dim connectionString
connectionString = "Provider=SQLOLEDB;Data Source=SERVER_NAME;Initial Catalog=DATABASE_NAME;User ID=YOUR_USERNAME;Password=YOUR_PASSWORD;"

' Open the connection
conn.Open connectionString

If Err.Number <> 0 Then
    WScript.Echo "Error connecting to the database: " & Err.Description
    WScript.Quit
End If

Dim rs
Set rs = CreateObject("ADODB.Recordset")

Dim query
query = "SELECT * FROM TableName"

' Execute the query
rs.Open query, conn

If Err.Number <> 0 Then
    WScript.Echo "Error executing query: " & Err.Description
Else
    ' Process the recordset as described earlier
End If

' Clean up
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing

With error handling, the script will provide meaningful feedback if there’s an issue with the connection or query execution.

Step 5: Closing the Connection and Cleaning Up

When working with databases, it’s essential to close connections and release resources once your tasks are complete. This prevents memory leaks and locks on your database.

Here’s how to close the connection and clean up at the end of your script:

' Close the connection and clean up
conn.Close
Set conn = Nothing

Complete Script

Here’s the full VBScript code, combining all the steps:

'how to connect to SQL Server with VBScript
On Error Resume Next

Dim conn
Set conn = CreateObject("ADODB.Connection")

Dim connectionString
connectionString = "Provider=SQLOLEDB;Data Source=SERVER_NAME;Initial Catalog=DATABASE_NAME;User ID=YOUR_USERNAME;Password=YOUR_PASSWORD;"

conn.Open connectionString

If Err.Number <> 0 Then
    WScript.Echo "Error connecting to the database: " & Err.Description
    WScript.Quit
End If

Dim rs
Set rs = CreateObject("ADODB.Recordset")

Dim query
query = "SELECT * FROM TableName"

rs.Open query, conn

If Err.Number <> 0 Then
    WScript.Echo "Error executing query: " & Err.Description
Else
    If Not rs.EOF Then
        Do Until rs.EOF
            WScript.Echo "Column1: " & rs("Column1") & ", Column2: " & rs("Column2")
            rs.MoveNext
        Loop
    Else
        WScript.Echo "No records found."
    End If
End If

rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing

Running the Script

To run the VBScript file:

1. Save the script as RunSQLQuery.vbs.
2. Double-click the file, or execute it from the command line using:

cscript RunSQLQuery.vbs

Conclusion

This VBScript guide shows you how to connect to SQL Server with vbscript, run queries, and handle results efficiently. With error handling and proper resource cleanup, this script can serve as a simple yet powerful tool for database automation tasks.