Querying other SQL databases using active script in WhatsUp


This is a script that allows you to query an SQL server other than the WhatsUp DB. It doesn’t require a DSN ODBC entry and is self contained.

Let’s assume we want to run this query:

SELECT cur_latency from MSreplication_monitordata where agent_type='3' and publication_id='2'

against a server named mySQLserver. To do that we can use the ADODB object in VBScript:

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")

'We're not using a DSN so specify the connection string
strConnectionString="DRIVER={SQL Server};SERVER=" & strDatabaseServer & ";UID=" & strUsername &_
";PWD=" & strPassword & ";DATABASE=" & strDatabaseName
context.logmessage strConnectionString

objConnection.Open strConnectionString
objRecordset.CursorLocation = adUseClient

This bit of the script creates a Connection object and using the connection string opens a connection to the SQL server. We then use that connection to perform our query:

'This is the query to run
strQuery="SELECT cur_latency from MSreplication_monitordata where agent_type='3' and publication_id='2'"
'Connect to the DB server and run the query
objRecordset.Open strQuery, objConnection, adOpenStatic,adLockOptimistic

The rows returned populate the record set object. We’re only expecting one row for this one so we can get the result by:

If objRecordset.EOF Then
    'No rows returned
    context.logmessage "Record cannot be found."
Else
    'rows returned
    context.logmessage objrecordset("cur_latency") 
    context.setvalue int(cur_latency)
End If

and then close the objects sets.

objRecordset.Close
objConnection.Close

Here’s the full script:

'The database server is kept in an attribute named "DatabaseServerName"
strDatabaseServer="mySQLserver"

'The database that stores the replication configuration is "distribution"
strDatabaseName="distribution"

'This is the query to run
strQuery="SELECT cur_latency from MSreplication_monitordata where agent_type='3' and publication_id='2'"

'Get the logon credentials
strUsername = Context.GetProperty("CredADO:Username")
strPassword = Context.GetProperty("CredADO:Password")

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")

'We're not using a DSN so specify the connection string
strConnectionString="DRIVER={SQL Server};SERVER=" & strDatabaseServer & ";UID=" & strUsername &_
";PWD=" & strPassword & ";DATABASE=" & strDatabaseName
context.logmessage strConnectionString

objConnection.Open strConnectionString
objRecordset.CursorLocation = adUseClient

'Connect to the DB server and run the query
objRecordset.Open strQuery, objConnection, adOpenStatic,adLockOptimistic

If objRecordset.EOF Then
    'No rows returned
    context.logmessage "Record cannot be found."
Else
    'rows returned
    context.logmessage objrecordset("cur_latency") 
    context.setvalue int(cur_latency)
End If

objRecordset.Close
objConnection.Close

Download the source from the link:

Download

Advertisements
Tagged , , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: