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

 

[tsuji]

[0] First you need to control the login, and then control the query on the table is well behaved, such as its existence.

[1] The return "strWatchFile" is not a string, but a recordset. (Decide if you want to keep the name.)

[2][tt]
Option Explicit
Dim objConnection, strWatchFile

on error resume next
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open "Provider=SQLOLEDB.1;Data Source=vanc-sql02\enterprisevault;Initial Catalog=EVJournalStore1","xxxxx","yyyyy"
if err.number<>0 then
MsgBox hex(err.number) & vbcrlf & err.description
else
set strWatchFile = objConnection.Execute("select count (*) from watchfile")
if err.number<>0 then
MsgBox hex(err.number) & vbcrlf & err.description
else
MsgBox "count: " & strWatchFile(0).value
strWatchFile.close
end if
set strWatchFile=nothing
objConnection.close
end if
on error goto 0

Set objConnection = Nothing[/tt]

 

[withanh]

Didn't realize that strWatchFile was a recordset. I thought I had to declare the recordset i.e.
set objRecordset = CreateObject("ADODB.Recordset")

But this works as I need it to, so thanks a ton for helping me with the code.

Connecting to databases has always been my Achilles heel, and since I don't do it often enough, by the time I do it again, I've forgotten what I knew.

Happy New Year!

h

 

[withanh]

tsuji,

I'd like to expand this a little further and have it read the databases on the server, then read the table names in the databases to find the appropriate table then run the count query against the found table & database. I found an old thread by you that I thought might work to get me started, but when I run it it errors on this line:

set objSQLServer = CreateObject("SQLDMO.SQLSERVER")

The error message is

ActiveX component can't create object: 'SQLDMO.SQLSERVER'

I'm relatively certain that I should be able to do this with ADO like I am when I hard code the db name and table names, but can't find any clues on how to achieve this.

My logic is:

open server and read database names into a collection
loop through the collection of databases and read the table names into a collection
loop through the collection of tables looking for the "watchfile" table and if it finds it run the count query against it

Thanks for any help!

h

 

[tsuji]

[3] To enumerate tables from a given database that you target, EVJournalStore1, you can simply query the table_name from its Information_Schema.tables. This often requires elevated privileges. The connection string remains the same, only you need to set up the correct query and to discover the table_name against your criteria. The outline of how it is done can be like this.
[tt]
dim rs
set rs = objConnection.Execute("select table_name from INFORMATION_SCHEMA.TABLES")
do while not rs.eof
msgbox rs("table_name").value
'do things if certain table_name is found
rs.movenext
loop
[/tt]

 

[withanh]

Thank you again! Is there a way to also enumerate the databases?

My Googling has led me to this

http://technet.microsoft.com/en-us/library/ms178534(SQL.90).aspx

and it appears that I can query sys.databases for name to list my db names, so in my connection string Do I put "sys.databases" as my "initial catalog="? then query "select * from name" or is master the initial catalog and query "select name from sys.databases"?

Thanks again!

h

 

[tsuji]

In that case, put
[tt] Initial Catalog=master[/tt]

 

[withanh]

OK, now when I run "select name from sys.databases" on the master db in query analyzer I get a list of db names. When I run this in my script it only returns "master"

Sub GetDatabaseNames
  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")
  i = 0
  objRecordset.MoveFirst
  Do While Not objRecordset.EOF
    MsgBox i
    MsgBox "Database name = " & objRecordset(i).Value & vbCrLf
    objRecordset.MoveNext
    i = i + 1
  Loop
  objRecordset.Close
  Set objRecordset = Nothing
  Set objConnection = Nothing
End Sub

h

 

[withanh]

OK, I see my error, but I don't understand it. The new code is:

Sub GetDatabaseNames
  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")
  i = 0
  objRecordset.MoveFirst
  Do While Not objRecordset.EOF
    MsgBox i
    MsgBox "Database name = " & objRecordset(0).Value & vbCrLf
    objRecordset.MoveNext
    i = i + 1
  Loop
  objRecordset.Close
  Set objRecordset = Nothing
  Set objConnection = Nothing
End Sub

I don't understand why ObjRecordset(0).Value, it seems like the index value ("(0)" right?) would change, but it doesn't. At least I have the data I want now. Now to put it into an array then loop through it looking for the "watchfile" table using the code you provided.

One other question, I keep opening and closing connections to the database, would it be more efficient to open the database once, run all my queries against it, then close it when I'm done?

Thanks again!!!

Happy New Year!

h

 

[tsuji]

That is what it see in your case; how about run it in sa account?

 

[withanh]

sa returned the same result, see my post that we made at the same time...

 

[tsuji]

You mean you see more in the studio?!

 

[withanh]

With my second set of code - using objRecordset(0).Value instead of objRecordset(i).Value I get the same database names that I get in the studio.

Now I'm trying to put those into an array so I can use them later.

 

[tsuji]

You don't need to know the column index of it, just refer it by name (or alias in case of applicable).
[tt] objRecordset("name").Value[/tt]

 

[withanh]

Oh, I get it! If I used objRecordset(1).Value, that would be the 2nd column in the recordset, right?

 

[withanh]

I'm having problems now putting this into an array, and I think the issue is objRecordset.RecordCount. When I try to ReDim arrDatabases(objRecordset.RecordCount), the value of objRecordset.RecordCount is -1. Shouldn't that return how many records are in the recordset?

 

[tsuji]

sure can discover the index in the studio.

 

[withanh]

Thoughts on using objRecordset.RecordCount? It returns -1 instead of how many records I have. I guess I can loop through the records and add a counter, then redim the array then loop through the records again putting them into the array. Doesn't seem very efficient, though.

 

[tsuji]

google recordcount.

 

[PHV]

putting this into an array
Have a look at the GetRows method of the ADODB.Recordset object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886