' ********************************************************************* ' ' Name : Verify ODBC connection & SQL statement.vbs ' Author : 3Ds (UK) Limited ' Description : Connect & run SQL against a given ODBC database. ' Script Type : Monitoring. ' Arguments : None. ' Returns : 0 - Success, 1 - An error occurred. ' Notes : Requires Windows Scripting Host (for VBScript). ' ' Change the "TODOs" for your own requirements. ' ' WIZARD:PARAMS=This script requires no parameters ' WIZARD:PARAMEXAMPLE= ' WIZARD:RESULTS=Return code ...||0 = OK|1 = An error prevented the script from running.||Script output also indicates "OK" for success, or "Error" with details of the faults found. ' ' ********************************************************************* ' Enable in-line error handling On Error Resume Next ' ------ Local declarations ------ Dim objDBConnection ' Database connection Dim objRecordSet ' Returned results (if required) Dim strConnectionString ' ODBC Connection string Dim strDSN ' ODBC System DSN Name - as in ODBCAD32 Dim strUser ' Database user Dim strPassword ' Database user password Dim strSQL ' SQL to run Dim intValue ' Holds our data Const adOpenStatic = 3 Const adLockOptimistic = 3 ' ------ Main Logic ------ ' Set up the connection parameters. The ultimate string is ' dependent on the type of database being accessed ' TODO: Change these parameters for your environment strDSN = "YourODBCConnection" strUser = "YourDatabaseUserName" strPassword = "YourDatabasePassword" ' Build our connection string strConnectionString = "PROVIDER=MSDASQL;DSN=" & strDSN & ";UID=" & strUser & ";PWD=" & strPassword & ";" ' Create an ADO connection & optionally recordset Set objDBConnection = CreateObject("ADODB.Connection") If Err.Number <> 0 Then ' Failed to map WScript.Echo "Error. Unable to create ADO connection object. " & Err.Description WScript.Quit (1) End If Set objRecordSet = CreateObject("ADODB.Recordset") If Err.Number <> 0 Then ' Failed to map Set objDBConnection = Nothing WScript.Echo "Error. Unable to create ADO recordset object. " & Err.Description WScript.Quit (1) End If ' Now connect to the target database objDBConnection.Open strConnectionString If Err.Number <> 0 Then ' Failed to map Set objRecordSet = Nothing Set objDBConnection = Nothing WScript.Echo "Error. Unable to connect to the database. " & Err.Description WScript.Quit (1) Else ' We're connected to the database ' TODO: Add your SQL here. It can be a SELECT or action statement such as INSERT or DELETE strSQL = "SELECT Column From Table" If Ucase(Left(strSQL, 6)) <> "SELECT" Then ' Execute SQL statement objDBConnection.Execute strSQL If Err.Number <> 0 Then ' SQL call failed objDBConnection.Close Set objRecordSet = Nothing Set objDBConnection = Nothing WScript.Echo "Error. Unable to execute SQL statement. " & Err.Description WScript.Quit (1) End If Else ' Access SELECT statement to create a recordset objRecordSet.Open strSQL, objDBConnection, adOpenStatic, adLockOptimistic If Err.Number <> 0 Then ' SQL call failed objDBConnection.Close Set objRecordSet = Nothing Set objDBConnection = Nothing WScript.Echo "Error. Unable to open recordset. " & Err.Description WScript.Quit (1) End If End If ' Access & move through the data as required objRecordSet.MoveFirst objRecordSet.MoveLast ' Close the recordset objRecordSet.Close End If ' Close the connection objDBConnection.Close ' ------ Cleanup ------ Set objRecordSet = Nothing Set objDBConnection = Nothing WScript.Echo "OK. ODBC connection & SQL query successful" WScript.Quit (0) ' ------ End of Script ------