Home » Scripts » Query a database with a monitor

Contoso.se

Welcome to contoso.se! My name is Anders Bengtsson and this is my blog about Azure infrastructure and system management. I am a senior engineer in the FastTrack for Azure team, part of Azure Engineering, at Microsoft.  Contoso.se has two main purposes, first as a platform to share information with the community and the second as a notebook for myself.

Everything you read here is my own personal opinion and any code is provided "AS-IS" with no warranties.

Anders Bengtsson

MVP
MVP awarded 2007,2008,2009,2010

My Books
Service Manager Unleashed
Service Manager Unleashed
Orchestrator Unleashed
Orchestrator 2012 Unleashed
OMS
Inside the Microsoft Operations Management Suite

Query a database with a monitor

I have seen a number of questions about how to run queries against a database and verify the answer. One way is to run a script inside a monitor. In this blog I wrote how to setup a script in a two state monitor. The script in this post will count number of  fields, if there are more then five, status of the monitor will be changed. Note that counting starts at 0 with fields collection.

 Const adOpenStatic = 3
Const adLockOptimistic = 3

Set oAPI = CreateObject(“MOM.ScriptAPI”)
Set oBag = oAPI.CreatePropertyBag()

Set objConnection = CreateObject(“ADODB.Connection”)
Set objRecordSet = CreateObject(“ADODB.Recordset”)

objConnection.Open _
“Provider=SQLOLEDB;Data Source=R2B1;” & _
“Trusted_Connection=Yes;Initial Catalog=ContosoConfiguration;” & _
“User ID=CORP\Administrator;Password=;”

objRecordSet.Open “SELECT * FROM roles”, _
objConnection, adOpenStatic, adLockOptimistic

varNo = objRecordSet.Record.Count

If varNO > 5 Then
Call oBag.AddValue(“SQL_Status”,”Error”)
Else
Call oBag.AddValue(“SQL_Status”,”Ok”)
End If

Call oAPI.Return(oBag)


3 Comments

Comments are closed.