database.vbs - vbscript script by ActiveXperts Software

database.vbs checks a database by executing an SQL query.

Use database.vbs directly from ActiveXperts Network Monitor; in the Manager's 'Monitor' menu, select 'New Check (Script)' and select database.vbs. Configure the required parameter, or press 'Load a working sample'.

In ActiveXperts Network Monitor, Administrators can use three different scripting languages: Powershell, VBScript and SSH.

---

database.vbs script code

' ///////////////////////////////////////////////////////////////////////////////
' // ActiveXperts Network Monitor  - VBScript based checks
' // For more information about ActiveXperts Network Monitor and VBScript, visit
' // http://www.activexperts.com/support/network-monitor/online/vbscript/
' ///////////////////////////////////////////////////////////////////////////////

Option Explicit

' Declaration of global variables
Dim   SYSDATA, SYSEXPLANATION   ' SYSDATA is displayed in the 'Data' column in the Manager; SYSEXPLANATION in the 'LastResponse' column

' Constants - return values
Const retvalUnknown = 1         ' ActiveXperts Network Monitor functions should always return True (-1, Success), False (0, Error) or retvalUnknown (1, Uncertain)


' // To test a function outside Network Monitor (e.g. using CSCRIPT from the
' // command line), remove the comment character (') in the following lines:
' Dim bResult
' bResult = CheckDatabase( "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=C:\Program Files (x86)\ActiveXperts\Network Monitor\Samples\Northwind.sdf", "SELECT * FROM Customers", 1 )
' WScript.Echo "Return value: [" & bResult & "]"
' WScript.Echo "SYSDATA: [" & SYSDATA & "]"
' WScript.Echo "SYSEXPLANATION: [" & SYSEXPLANATION & "]"


Function CheckDatabase( strConnectionString, strSqlQuery, nMinimumNumRecords )
' Description: 
'     Checks database existence by counting the number of records in a database table
' Parameters:
'     1) strConnectionString As String - An OLE/DB connection string
'     2) strQuery As String - SQL Query to be executed
'     3) nMinimumNumRecords As Number - Minimum number of records retrieved by executing query 'strQuery'
' Usage:
'     CheckDatabase( "<OLE/DB Connection String>"", "<Query>", <Minimum_Num_Records> )
' Sample:
'     CheckDatabase( "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=C:\Program Files (x86)\ActiveXperts\Network Monitor\Samples\Northwind.sdf", "SELECT * FROM Customers", 1 )

  Dim objConn, RS, nNumRecords
  
  CheckDatabase   = retvalUnknown  ' Default return value, and will be shown as a yellow (uncertain) icon in the Manager
  SYSDATA         = ""             ' SYSDATA displayed in the 'Data' column in the Manager          
  SYSEXPLANATION  = ""             ' SYSEXPLANATION displayed in the 'LastResponse' column in the Manager
  
  nNumRecords     = 0
  

  Set objConn           = CreateObject( "ADODB.Connection" )
On Error Resume Next
  objConn.Open strConnectionString
  If( Err.Number <> 0 ) Then
    CheckDatabase     = retvalUnknown
    SYSDATA           = ""
    SYSEXPLANATION    = "Open database failed; Error 0x" & Hex( Err.Number ) & ": " & Err.Description
    Exit Function
  End If
On Error Goto 0

On Error Resume Next

  Set RS              = objConn.Execute( strSqlQuery )
  If( Err.Number <> 0 ) Then
    CheckDatabase     = retvalUnknown
    SYSDATA           = ""
    SYSEXPLANATION    = "Execute query failed; Error 0x" & Hex( Err.Number ) & ": " & Err.Description
    Exit Function
  End If
On Error Goto 0

  While( Not RS.EOF )
   nNumRecords = nNumRecords + 1
   RS.MoveNext
  WEnd

  if( nNumRecords >= nMinimumNumRecords ) Then 
    CheckDatabase     = True
   Else
    CheckDatabase     = False
  End If 

  SYSDATA             = nNumRecords
  SYSEXPLANATION      = "Database checked, #records found=[" & nNumRecords & "], minimum required=[" & nMinimumNumRecords & "]"

  RS.Close

  objConn.Close

  Set RS = Nothing
  Set objConn = Nothing
	
End Function