VBScript – Sample SQL Server App

Background

On the other sign of a day long troubleshooting session.

Wanted to share tiny code snippets that one can use to test out an Application that connects to databases; in this case Microsoft SQL Server and MS Access.

 

Code

Script


' enforces variable names and declarations

option explicit

on error resume next

REM ************************* CONNECTION STRING	******************************

REM ADODB.Connection: Provider cannot be found. It may not be properly installed.

Const CONNECTION_STRING ="Provider=SQLNCLI10.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=."
REM Const CONNECTION_STRING ="Provider=SQLNCLI11;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=."

REM ************************* CONNECTION STRING	******************************

Dim Connection

Dim Recordset

Dim SQL

Dim Server

REM Dim field

Dim strSQLInstance 
Dim strDBName

Dim strLog

Dim CHAR_TAB

CHAR_TAB = vbTab

'declare the SQL statement that will query the database
SQL = "SELECT sqlInstance=cast(serverproperty('servername') as sysname), dbName = db_name()"
 

'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 CONNECTION_STRING

if (Err.Number <> 0)  Then

	strLog = ""
	strLog = strLog & "Error" & vbCrLf
	strLog = strLog & "====" & vbCrLf	
	strLog = strLog & vbTab & "Connection String :- " & CONNECTION_STRING & vbCrLf
	strLog = strLog & vbTab & "Error Number :-  " & CSTR(Err.Number) & vbCrLf
	strLog = strLog & vbTab & "Error Description :-   " & CSTR(Err.Description) & vbCrLf
	
	wscript.echo strLog
	
	WScript.Quit 

end if


 

'Open the recordset object executing the SQL statement and return records 

Recordset.Open SQL,Connection

 

'first of all determine whether there are any records 

If Recordset.EOF Then 

	wscript.echo "There are no records to retrieve; Check that you have the correct job number."

Else 

	'if there are records then loop through the fields 

	Do While NOT Recordset.Eof   


		strSQLInstance = Recordset("sqlInstance")

		strDBName = Recordset("dbName")		
		
		strLog = "SQL Instance :- " + strSQLInstance _
					+ CHAR_TAB _
					+ "DB :- " + strDBName

		if strLog <> "" then

			wscript.echo strLog

		end if

		Recordset.MoveNext     

	Loop

End If

 

'close the connection and recordset objects to free up resources

Recordset.Close

Set Recordset=nothing

Connection.Close

Set Connection=nothing

Output

Success

 

Failure

ADODB.Connection: Provider cannot be found. It may not be properly installed.

If a different version of SQL Server Client is installed or client is not installed, we will get the error listed above.

 

Troubleshooting

Registry

Launch registry editor (regedit) and search for SQLNCLI..

Here we find SQLNCLI11.

 

Source Control

GitHub

Posted to GitHub here

 

References

  1. PowerASP
    • VBScript Constants
      Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s