Get result of SQL count query into a variable 2

[withanh]

I'm using Munin (

http://www.munin-monitoring.org)

to monitor my servers. It is a Linux monitoring tool, but there is a Windows module available.

One of the items I want to monitor is how many records are contained in a certain table on an MSSQL server. I have a basic idea of how to connect from searching through this forum and googling for other help, but I'm stuck.

Option Explicit
Dim objConnection, strWatchFile

Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open "Provider=SQLOLEDB.1;Data Source=vanc-sql02\enterprisevault;Initial Catalog=EVJournalStore1","xxxxx","yyyyy"

strWatchFile = objConnection.Execute("select count (*) from watchfile")

MsgBox strWatchFile

Set objConnection = Nothing

The error is on the line "MsgBox strWatchFile" line, the error is "Wrong number of arguments or invalid property assignment".

When I run the "select count (*) from watchfile" in SQL Mgmt Studio, it returns an integer value. I need to know how to get the integer value into the variable so I can have my way with it.

Thanks!!!

h

 

[withanh]

I'll keep googling, but so far this is what I came up with, but it yields an empty array:

arrDatabases = objRecordset.GetRows(,0,"name")

 

[withanh]

OK, got it. I just did the .GetRows with no parameters, so my final code is

  Set objConnection = CreateObject("ADODB.Connection")
  objConnection.Open "Provider=SQLOLEDB.1;Data Source=" & strDBServer & ";Initial Catalog=master",strDBUsername,strDBPassword
  Set objRecordset = objConnection.Execute("select name from sys.databases")
  objRecordset.MoveFirst
  arrDatabases = objRecordset.GetRows()
  objRecordset.Close
  Set objRecordset = Nothing
  objConnection.Close
  Set objConnection = Nothing

And this yields the proper results:

For i = 0 To UBound(arrDatabases,2)
  WScript.StdOut.Write arrDatabases(0,i) & vbCrLf
Next

 

[withanh]

I'm having problems looping through my databases now. The code is

Sub GetDatabaseNames2()
  GetDatabases
  For Each objDatabase In arrDatabases
    GetTables
    For Each objTable In arrTables
      MsgBox "Database = " & objDatabase & vbCrLf & "Table = " & objTable
    Next
  Next
End Sub

Sub GetTables()
  Set objConnection = CreateObject("ADODB.Connection")
  strDBConnection = "Provider=SQLOLEDB.1;Data Source=" & strDBServer & ";Initial Catalog=" & objDatabase
  objConnection.Open strDBConnection,strDBUsername,strDBPassword
  Set objRecordset = objConnection.Execute("select table_name from information_schema.tables")
  objRecordset.MoveFirst
  arrTables = objRecordset.GetRows()
  objRecordset.Close
  objConnection.Close
  Set objRecordset = Nothing
  Set objConnection = Nothing
End Sub

Sub GetDatabases()
  Set objConnection = CreateObject("ADODB.Connection")
  strDBConnection = "Provider=SQLOLEDB.1;Data Source=" & strDBServer & ";Initial Catalog=master"
  objConnection.Open strDBConnection,strDBUsername,strDBPassword
  Set objRecordset = objConnection.Execute("select name from sys.databases")
  objRecordset.MoveFirst
  arrDatabases = objRecordset.GetRows()
  objRecordset.Close
  objConnection.Close
  Set objRecordset = Nothing
  Set objConnection = Nothing
End Sub

For the first database in arrDatabases, it lists all table names properly, when it gets to the second database it throws an error in the GetTables() sub on the line objRecordset.MoveFirst

ADODB.Recordset : Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

Do I need to use an array of recordsets? like objrecordset(i) and each time through add i=i+1?

I'm confused because each time I am closing the recordset, closing the connection and setting both to nothing, which should allow me to reuse them (at least in my mind it should).

Thanks!

 

[withanh]

Wow, I just surprised myself, I figured it out. The problem is the table was empty so I had to check for that like this

  If Not objRecordset.EOF Then
    objRecordset.MoveFirst
    arrTables = objRecordset.GetRows()
  End If

Problem solved. Now it doesn't like opening the "model" table, but that's easy enough to trap for.

Woo hoo!

 

[withanh]

I finally have everything dialed in and working the way I want. Now I am soliciting advice for optimizing the code.

Thanks!

Option Explicit

Dim objConnection, objRecordset
Dim strDBServer, strDBUsername, strDBPassword, strDBConnection
Dim arrDatabases(), arrFindDatabases, objDatabase, arrTables, objTable, OpenDatabase, x

'Set Database variables here
strDBServer = "DB_Server"
strDBUsername = "DB_Username"
strDBPassword = "DB_Password"

'Create ADO connection
Set objConnection = CreateObject("ADODB.Connection")

BuildMuninOutput

'Release the ADO connection
Set objConnection = Nothing

Sub BuildMuninOutput()
  If WScript.Arguments.Count = 0 Then
    GetDatabaseNames
    For Each objDatabase In arrDatabases
      GetWatchFileCount objDatabase
    Next
    WScript.StdOut.Write(".") & vbLf
  ElseIf WScript.Arguments.Count = 1 Then
    If WScript.Arguments.Item(0) = "config" Then
      GetDatabaseNames
      WScript.StdOut.Write("graph_title Enterprise Vault Items Awaiting Backup") & vbLf
      WScript.StdOut.Write("graph_category vault") & vbLf
      WScript.StdOut.Write("graph_info Shows Enterprise Vault items awaiting backup") & vbLf
      WScript.StdOut.Write("graph_vlabel Item Count") & vbLf
      WScript.StdOut.Write("graph_scale no") & vbLf
      WScript.StdOut.Write("graph_printf %6.0lf") & vbLf
      WScript.StdOut.Write("graph_args --base 1000 -l 0 ") & vbLf
      For Each objDatabase In arrDatabases
        WScript.StdOut.Write(objDatabase & ".label " & objDatabase) & vbLf
        WScript.StdOut.Write(objDatabase & ".info " & objDatabase & " items awaiting backup") & vbLf
        WScript.StdOut.Write(objDatabase & ".warning 75000") & vbLf
        WScript.StdOut.Write(objDatabase & ".critical 100000") & vbLf
      Next
      WScript.StdOut.Write(".") & vbLf
    ElseIf WScript.Arguments.Item(0) = "name" then
      WScript.StdOut.Write("ev_awaiting_backup")
    End If
  End If
End Sub

Sub GetDatabaseNames()
  FindDatabases
  For Each objDatabase In arrFindDatabases
    'Didn't know a better way to filter out the system databases
    If LCase(objDatabase) <> "master" And LCase(objDatabase) <> "model" And LCase(objDatabase) <> "msdb" And LCase(objDatabase) <> "tempdb" Then
      GetTables
      If OpenDatabase = True Then
        For Each objTable In arrTables 
          If CStr(objTable) = "WatchFile" Then
            ReDim Preserve arrDatabases(x)
            arrDatabases(x) = objDatabase
            x = x + 1
            Exit For
          End If
        Next
      End If
    End If
  Next
End Sub

Sub GetTables()
  On Error Resume Next
  strDBConnection = "Provider=SQLOLEDB.1;Data Source=" & strDBServer & ";Initial Catalog=" & objDatabase
  objConnection.Open strDBConnection,strDBUsername,strDBPassword
  Set objRecordset = objConnection.Execute("select table_name from information_schema.tables")
  If Err.Number = 0 Then 'Only retrieve tables if we can open the database
    objRecordset.MoveFirst
    arrTables = objRecordset.GetRows()
    OpenDatabase = True
  Else
    OpenDatabase = False
  End If
  objRecordset.Close
  objConnection.Close
  Set objRecordset = Nothing
  On Error Goto 0
End Sub

Sub FindDatabases()
  strDBConnection = "Provider=SQLOLEDB.1;Data Source=" & strDBServer & ";Initial Catalog=master"
  objConnection.Open strDBConnection,strDBUsername,strDBPassword
  Set objRecordset = objConnection.Execute("select name from sys.databases")
  objRecordset.MoveFirst
  arrFindDatabases = objRecordset.GetRows()
  objRecordset.Close
  objConnection.Close
  Set objRecordset = Nothing
End Sub

Sub GetWatchFileCount(VaultStore)
  strDBConnection = "Provider=SQLOLEDB.1;Data Source=" & strDBServer & ";Initial Catalog=" & VaultStore
  objConnection.Open strDBConnection,strDBUsername,strDBPassword
  Set objRecordset = objConnection.Execute("select count (*) from watchfile")
  WScript.StdOut.Write VaultStore & ".value " & objRecordset(0).Value & vbLf
  objRecordset.Close
  objConnection.Close
  Set objRecordset = Nothing
End Sub