Connect to MSSQL database

Posted 21 October, 2008 in Other Stuff

The following code can be used in VBScript to connect to a database. It uses both a timout and ping to make sure there is a connection to the database.

This is very handy when creating database driven Scala Infochannel scripts. It also uses error traping to make sure the script does not stop running when no connection was made so that you can use an alternative source of data such as text files.

' These are the database connection details. Change the IP address, Username and Password.
DBdetails = "Driver=SQL Server; Server=IPADDRESS; Database=sms; Uid=USERNAME; Pwd=PASSWORD"

' This is the IP address again
DBipaddress = "IPADDRESS"

On Error Resume Next ' Enable error handling

' Try to ping the database
 Set WshShell = CreateObject("WScript.Shell")
 PINGFlag = Not CBool(WshShell.run("ping -n 1 " & DBipaddress,0,True))
 If PINGFlag = True Then
  ' Connect to the db
  Set myConnection = CreateObject("ADODB.Connection")
  myConnection.ConnectionTimeout = 5
  myConnection.Open DBdetails
 Else
  Err.Number = 1
 End If

If Err.Number <> 0 Then ' There was an error when connecting to the DB.

Else

 ' Code goes here if database was contacted

End If

-*O*-