Home » Too many SQL connections

Too many SQL connections

Quote:

We have  many SQL 2000 server, that is not setup as a distributor or a

publisher. It is not involved in replication. we we have been seeing a bunch of connections on the server from user NT AUTHORITY/SYSTEM application SQLDMO executing exec sp_helpdistpublisher @check_user = 1 a number of times. Why would it be doing that, when the server is not setup for replication. One more thing.. We have MOM2005 SP1 would this be related.. ??

I’m getting the same problem. After installing the new SQL MP the connections arn’t released. So I started the query analyzer. Exec a sp_who2. The process names where all SQLDMO_ and the SQL statement was  exec sp_helpdistpublisher @check_user = 1 . I started a SQL profiles and monitored the server for a while. Then i did a search string with “exec sp_helpdistpublisher @check_user = 1”. On the match record i looked at the “clientProcessID” . Then i looked in the system task manager what process has this PID. And yes it was the “MOMHost.exe”. So YES it is MOM releated. I’m going to see whats the solution. 

Found it on the inet:

Quote:

RESOLUTION:

To resolve the issue, you changed the contents of the SQL Server 2000
Replication Monitoring script with the contents of the one from the original
SQL 2000 MP (version 05.0.2746.0000). Since then, the process is now being
released.

Ok this solves the problem. I pasted the 05.0.2746.0000 script below:

‘——————————————————————-
‘ <company>Microsoft Corporation</company>
‘ <copyright>Copyright (c) Microsoft Corporation 2003</copyright>
‘ <summary>
‘   Alerts on Replication failures
‘ </summary>
‘——————————————————————-

option explicit

‘Event Constants
Const EVENTID_INFORMATION = 10701

Const PROBLEMSTATE_NOTSET = 0
Const PROBLEMSTATE_GREEN = 1
Const PROBLEMSTATE_GREY = 2
Const PROBLEMSTATE_RED = 3

Const ALERT_SUCCESS = 10
Const ALERT_INFORMATION = 20
Const ALERT_WARNING = 30
Const ALERT_ERROR = 40
Const ALERT_CRITICAL_ERROR = 50
Const ALERT_SECURITY_BREACH = 60
Const ALERT_SERVICE_UNAVAILABLE = 70

‘Other constants
Const SCRIPT_NAME = “SQL Server 2000 Replication Failures”

Dim g_oSQL

Sub Main()

 Dim oParams, bInformationEvent, aInstances, sMsgActive
 Dim i, sMessage, oEvent

 Set g_oSQL = New SQL

 Set oParams = ScriptContext.Parameters
 bInformationEvent = CBool(oParams.Get(“InformationEvent”))
 Set oParams = Nothing

 aInstances = Split(g_oSQL.GetSQLInstances(),”,”)
 For i = 0 To UBound(aInstances)
  If sMsgActive <> “” Then sMsgActive = sMsgActive & “,”  
  sMsgActive = sMsgActive & aInstances(i)

  CheckReplicationFailures aInstances(i)
 Next
 
 If bInformationEvent Then
  If sMsgActive <> “” Then
   sMessage = “The script ‘” & SCRIPT_NAME & “‘ ran.  The following instances are available on this server and were checked: ” & sMsgActive
  Else
   sMessage = “The script ‘” & SCRIPT_NAME & “‘ ran.  There were no instances currently active on this server”
  End If
  g_oSQL.CreateEvent EVENTID_INFORMATION, EVENT_TYPE_INFORMATION, sMessage
    End If

End Sub

‘******************************************************************************
‘ Name:  CheckReplicationFailures
‘
‘ Purpose: Checks for replication failures
‘
‘ Parameters: sInstance – the name of the instance to check
‘
‘ Returns: nothing
‘
Function CheckReplicationFailures(sInstance)
 dim cnADOConnection
 dim rsSQLReplicationFailures
 dim strServerName
 dim strProv
 dim strQuery
 dim strDatabaseName
 dim sName
 dim spath
 dim sMessage

 strDatabaseName = “distribution”
 
 On Error Resume Next

 sName = g_oSQL.GetSQLName(sInstance)

 If sName <> “” Then

  If sInstance = “MSSQLSERVER” Then
  sPath = sName
  Else
  sPath = sName & “” & sInstance
  End If

  Set cnADOConnection = MomCreateObject(“ADODB.Connection”)
  cnADOConnection.Provider = “sqloledb”
  cnADOConnection.ConnectionTimeout = 30
  strProv = “Server=” & sPath & “;Database=” & strDatabaseName & “;Trusted_Connection=yes”

  on error resume next
  Err.Clear
  cnADOConnection.Open strProv
  if 0 <> Err.number then
   on error goto 0
   Set cnADOConnection = Nothing
   Exit Function
  end if
  on error goto 0

  ’SELECT
  ’ dbo.MSdistribution_history.agent_id,
  ’ dbo.MSdistribution_history.runstatus,
  ’ dbo.MSdistribution_history.[time],
  ’ dbo.MSdistribution_history.comments,
  ’ dbo.MSdistribution_agents.name
  ’FROM
  ’ dbo.MSdistribution_history INNER JOIN
  ’ dbo.MSdistribution_agents ON dbo.MSdistribution_history.agent_id = dbo.MSdistribution_agents.id
  ’WHERE    
  ’ (dbo.MSdistribution_history.runstatus = ‘6’) AND
  ’ DATEDIFF(minute, [time], getdate()) < 10 
  strQuery = “SELECT dbo.MSdistribution_history.agent_id, dbo.MSdistribution_history.runstatus, dbo.MSdistribution_history.[time], dbo.MSdistribution_history.comments, dbo.MSdistribution_agents.name FROM dbo.MSdistribution_history INNER JOIN dbo.MSdistribution_agents ON dbo.MSdistribution_history.agent_id = dbo.MSdistribution_agents.id WHERE (dbo.MSdistribution_history.runstatus = ‘6’) AND DATEDIFF(minute, [time], getdate()) < 20”

  Set rsSQLReplicationFailures = cnADOConnection.Execute(strQuery)

  Do While rsSQLReplicationFailures.EOF = False

            sMessage = “The SQL Server Replication Agent (” & rsSQLReplicationFailures(“name”).Value & “) has failed. Details: ” & rsSQLReplicationFailures(“comments”).Value
   g_oUtil.LogMessage g_oUtil.DBG_TRACE, sMessage

   g_oSQL.CreateAlert ALERT_CRITICAL_ERROR, _
    SCRIPT_NAME, _
    sMessage, _
    ””, _
    ””

   rsSQLReplicationFailures.MoveNext
  Loop

 End If

 Set cnADOConnection = Nothing
 Set rsSQLReplicationFailures = Nothing
End Function

Dim g_aDatabaseExcludeArray
Dim g_bDatabaseExclusion
Dim g_aJobExcludeArray
Dim g_bJobExclusion

Const SQL_CLASS_ID = “SQL”

Const SQL_SQL_SERVER_INSTANCE_NAME_ATTRIBUTE_ID =           “SQL Server Instance Name”
Const SQL_AUDIT_LEVEL_ATTRIBUTE_ID =                        “Audit Level”
Const SQL_AUTHENTICATION_MODE_ATTRIBUTE_ID =                “Authentication Mode”
Const SQL_CLUSTER_ATTRIBUTE_ID =                            “Cluster”
Const SQL_CONNECTION_NAME_ATTRIBUTE_ID =                    “Connection Name”
Const SQL_ENABLE_ERROR_REPORTING_ATTRIBUTE_ID =             “Enable Error Reporting”
Const SQL_ERROR_LOG_LOCATION_ATTRIBUTE_ID =                 “Error Log Location”
Const SQL_LANGUAGE_ATTRIBUTE_ID =                           “Language”
Const SQL_MASTER_DATABASE_LOCATION_ATTRIBUTE_ID =           “Master Database Location”
Const SQL_MASTER_DATABASE_LOG_LOCATION_ATTRIBUTE_ID =       “Master Database Log Location”
Const SQL_REPLICATION_ATTRIBUTE_ID =                        “Replication”
Const SQL_REPLICATION_DISTRIBUTION_DATABASE_ATTRIBUTE_ID =  “Replication Distribution Database”
Const SQL_REPLICATION_WORKING_DIRECTORY_ATTRIBUTE_ID =      “Replication Working Directory”
Const SQL_SERVICE_NAME_ATTRIBUTE_ID =                       “Service Name”
Const SQL_SERVICE_PACK_VERSION_ATTRIBUTE_ID =               “Service Pack Version”
Const SQL_SQL_SERVER_VERSION_ATTRIBUTE_ID =                 “SQL Server Version”
Const SQL_AGENT_NAME_ATTRIBUTE_ID =                         “Agent Name”
Const SQL_INSTALL_PATH_ATTRIBUTE_ID =                       “Install Path”
Const SQL_TOOLS_PATH_ATTRIBUTE_ID =                         “Tools Path”

Const SQL_LOG_SPACE_COMPONENT_ID =                          “Log Space”
Const SQL_DB_HEALTH_COMPONENT_ID =                          “DB Health”
Const SQL_AGENT_COMPONENT_ID =                              “Agent”
Const SQL_CONNECTIVITY_COMPONENT_ID =                       “Connectivity”
Const SQL_DB_SPACE_COMPONENT_ID =                           “DB Space”
Const SQL_SQL_COMPONENT_ID =                                “SQL”

‘******************************************************************************
‘ Name – SQL – SQL Server Utility Class
‘
‘ Version – Microsoft Operations Manager 2005
‘
‘ Purpose – Utility Class for SQL Server health monitoring
‘
‘ Methods Implemented –
‘
‘ Discovery and Service Check:
‘  GetSQLInstances  - Returns a comma-delimited list of SQL instances
‘  IsSQL7    - Returns True if SQL Server 7.0
‘  Is64Bit    - Returns True if 64Bit
‘  IsMSDE    - Returns True if MSDE
‘  IsClustered   - Returns boolean for SQL clustered value
‘  IsServiceStarted - Checks if the specified service is started
‘  GetSQLName   - Returns the name of the SQL Server 
‘  GetSQLInstanceName - Returns the SQL Server instance name
‘  CollectDatabaseExclusions - Collect a list of excluded servers
‘       CollectJobExclusions    – Collect a list of excluded jobs
‘  IsDatabaseExcluded   - Determine if a database is excluded
‘       IsJobExcluded       – Determine if a job is excluded
‘       IsSQLServiceStarted – Checks if the specified SQL server instance is started
‘       GetSQLServiceName   – Returns the service name for a SQL instance
‘       GetConnectionFailureMessage – Returns an error message for connection failures
‘       GetRunningInstances  – Returns arrays of the instances in a given array that are running and not running
‘
‘ Connectivity Check:
‘  SQLConnect   - Checks if a specified instance returns data
‘
‘ Event Logging:
‘  CreateEvent   - Creates a MOM event
‘       CreateAlert         – Creates a MOM alert
‘
‘ Service Discovery:
‘  ServiceStateUpdate - Updates the Summary State View for a specified SRG
‘
‘ Registry Reading:  
‘  ReadRegistryStringValue - Read a string registry key
‘  ReadRegistryMultiStringValue – Read a multi string registry key
‘  ReadRegistryDWORDValue – Read a DWORD value registry key
‘       GetSQLInstanceKeyRoot – Get the root of the sql instance’s registry keys
‘       GetSQLInstanceKey     – Get the registry key path for a sql instance
‘  
‘ Version 12

Class SQL
 
  Public HKEY_LOCAL_MACHINE
  Public SQL_DEFAULT
  Public SQL_KEY_DEFAULT
  Public SQL_KEY_NAMED
  Public SQL_KEY_VER
  Public SQL_VAL_INSTINST
 
  Public SERVICESTATE_GREEN
  Public SERVICESTATE_YELLOW
  Public SERVICESTATE_RED
 
  Public DATABASE_EXCLUDE_FILENAME
  Public DATABASE_EXCLUDE_DIRECTORY
  Public JOB_EXCLUDE_FILENAME
  Public JOB_EXCLUDE_DIRECTORY
 
  Private m_oSafeRegistry
 
‘******************************************************************************
‘ Name:  Class_Initialize
‘
‘ Purpose: Initialize the public methods of the class 
‘
‘ Parameters: None
‘
‘ Returns: None
‘
Private Sub Class_Initialize()

    HKEY_LOCAL_MACHINE = &H80000002
    SQL_DEFAULT = “MSSQLSERVER”
    SQL_KEY_DEFAULT = “SoftwareMicrosoftMSSQLServer”
    SQL_KEY_NAMED = “SoftwareMicrosoftMicrosoft SQL Server”
    SQL_KEY_VER = “SoftwareMicrosoftMSSQLServerMSSQLServerCurrentVersion”
    SQL_VAL_INSTINST = “InstalledInstances”

    SERVICESTATE_GREEN = 0
    SERVICESTATE_YELLOW = 1
    SERVICESTATE_RED = 2
   
    DATABASE_EXCLUDE_FILENAME = “SQLExclude.txt”
    DATABASE_EXCLUDE_DIRECTORY = “C:”
    JOB_EXCLUDE_FILENAME = “SQLMPAgentExclude.txt”
    JOB_EXCLUDE_DIRECTORY = “C:”

    Set m_oSafeRegistry = New SafeRegistry
    m_oSafeRegistry.SuppressionFlags = (m_oSafeRegistry.SUPPRESS_KEY_NOT_FOUND Or m_oSafeRegistry.SUPPRESS_VALUE_NOT_FOUND)
   
End Sub

‘******************************************************************************
‘ Name:  GetRunningInstances
‘
‘ Purpose: 
‘
‘ Parameters: 
‘
‘ Returns: 
‘
Public Function GetRunningInstances(ByVal aInstances, ByRef aNonRunningInstances)

    Dim sWQLNameList
    Dim sInstance
   
    If Not IsArray(aInstances) Then Exit Function
   
    For Each sInstance In aInstances
        If sWQLNameList <> “” Then sWQLNameList = sWQLNameList & ” or “
        sWQLNameList = sWQLNameList & “Name = ‘” & GetSQLServiceName(sInstance) & “‘”
    Next
   
    If sWQLNameList <> “” Then
        Dim sWQLQuery
        sWQLQuery = “select Name from Win32_Service where (” & sWQLNameList & “)”
       
        Dim sNamespace
        sNamespace = “winmgmts://” & ScriptContext.TargetComputer & “/root/cimv2”
       
        Dim oRunning
        Set oRunning = WMIExecQuery(sNamespace, sWQLQuery & ” and State = ‘Running'”)
       
        GetRunningInstances = CreateSQLInstanceArray(oRunning)
       
        Dim oNotRunning
        Set oNotRunning = WMIExecQuery(sNamespace, sWQLQuery & ” and State <> ‘Running'”)
       
        aNonRunningInstances = CreateSQLInstanceArray(oNotRunning)
    End If

End Function

‘******************************************************************************
‘ Name:  CreateSQLInstanceArray
‘
‘ Purpose: 
‘
‘ Parameters: 
‘
‘ Returns: 
‘
Private Function CreateSQLInstanceArray(ByVal oServiceObjectSet)
    Dim aInstances
   
    If oServiceObjectSet.Count > 0 Then
        ReDim aInstances(oServiceObjectSet.Count – 1)
        Dim i
        i = 0
        Dim oService
        For Each oService in oServiceObjectSet
            aInstances(i) = GetSQLInstanceNameFromServiceName(oService.Name)
            i = i + 1
        Next
    End If

    CreateSQLInstanceArray = aInstances       
End Function

‘******************************************************************************
‘ Name:  GetSQLInstanceKeyRoot
‘
‘ Purpose: Gets the path to the root registry key for the instance’s
‘           registry values
‘
‘ Parameters: sInstance – The SQL instance name
‘
‘ Returns: the root registry path
‘
Public Function GetSQLInstanceKeyRoot(ByVal sInstance)
    If sInstance = SQL_DEFAULT Then
        GetSQLInstanceKeyRoot = SQL_KEY_DEFAULT
    Else
        GetSQLInstanceKeyRoot = SQL_KEY_NAMED & “” & sInstance
    End If
End Function

‘******************************************************************************
‘ Name:  GetSQLInstanceKey
‘
‘ Purpose: Gets the path to the registry key for the instance
‘
‘ Parameters: sInstance – The SQL instance name
‘
‘ Returns: the registry path
‘
Public Function GetSQLInstanceKey(ByVal sInstance, ByVal sKey)
    GetSQLInstanceKey = GetSQLInstanceKeyRoot(sInstance) & “” & sKey
End Function

‘******************************************************************************
‘ Name:  GetConnectionFailureMessage
‘
‘ Purpose: To generate a message stating the reason for failing to connect to a SQL instance
‘
‘ Parameters: sInstance, The SQL instance
‘               lErrNumber, The error number returned from the connection attempt
‘               sErrDescription, The error description returned from the connection attempt
‘
‘ Returns: The failure message
‘
Public Function GetConnectionFailureMessage(sInstance, lErrNumber, sErrDescription)
    Dim sFailureReason
    Dim sSQLServiceName
    sSQLServiceName = GetSQLServiceName(sInstance)
    Dim sResult

    Select Case IsSQLServiceStarted(sInstance)
        Case -2
            sFailureReason = “is not installed”
        Case -1, 0
            sFailureReason = “is not running”
        Case 1
            Const DB_CONNECTION_FAILURE_MESSAGE = “The SQL Server management pack script “”SQL Server 2000 Replication Monitoring”” is unable to successfully connect to the SQL Server instance “”{ServiceName}””. The error message returned is “”{Description}”””
            sResult = Replace(DB_CONNECTION_FAILURE_MESSAGE, “{ServiceName}”, sSQLServiceName)
            GetConnectionFailureMessage = Replace(sResult, “{Description}”, sErrDescription)
            Exit Function
    End Select

    Const DB_CONNECTION_NO_SERVICE_MESSAGE = “The SQL Server service ({ServiceName}) {FailureReason}.”
   
    sResult = Replace(DB_CONNECTION_NO_SERVICE_MESSAGE, “{ServiceName}”, sSQLServiceName)
    GetConnectionFailureMessage = Replace(sResult, “{FailureReason}”, sFailureReason)
End Function

‘******************************************************************************
‘ Name:  GetSQLInstances
‘
‘ Purpose: Gets the list of instances of SQL installed on
‘  the specified server.  These are read from the registry.
‘
‘ Parameters: None
‘
‘ Returns: A comma separated list of instances
‘
Public Function GetSQLInstances()

 Dim sInstances
 Dim aKeyValues
 Dim iCount
    Dim sClusterName
    Dim bAddInstance
    Dim sInstance
   
 aKeyValues = ReadRegistryMultiStringValue(SQL_KEY_NAMED, SQL_VAL_INSTINST, ScriptContext.TargetComputer)
  
 If Not IsNull(aKeyValues) Then
  For Each sInstance In aKeyValues
   If Not IsMSDE(sInstance) Then
       sClusterName = ReadRegistryStringValue(GetSQLInstanceKey(sInstance, “Cluster”), “ClusterName”, ScriptContext.TargetComputer)
       If ScriptContext.IsTargetVirtualServer Then
           bAddInstance = (ScriptContext.TargetNetbiosComputer = sClusterName)
       Else
           bAddInstance = IsNull(sClusterName)
       End If
      
       If bAddInstance Then
        If sInstances <> “” Then
         sInstances = sInstances & “,”
        End If
        
        sInstances = sInstances & sInstance
    End If
   End If
  Next
 Else
  If IsSQL7(ScriptContext.TargetComputer) then
   sInstances = SQL_DEFAULT
  Else
   sInstances = “”
  End If
 End If

 Call g_oUtil.LogMessage(g_oUtil.DBG_TRACE, “GetSQLInstances returning the following instances: ” & sInstances)

 GetSQLInstances = sInstances

End Function

‘******************************************************************************
‘ Name:  IsSQL7
‘
‘ Purpose: Checks if a Server is SQL Server 7.0
‘
‘ Parameters: sHostName, the name of the instance to check
‘
‘ Returns: True if Microsoft SQL Server 7.0 is installed
‘   False if Microsoft SQL Server 7.0 is not installed
‘
Public Function IsSQL7(sHostName)

 If Left(ReadRegistryStringValue(SQL_KEY_VER, “CurrentVersion”, ScriptContext.TargetComputer),4) = “7.00” Then
  IsSQL7 = True
  Call g_oUtil.LogMessage(g_oUtil.DBG_TRACE, “SQL Server 7.0 True for: ” & sHostName)
 Else
  IsSQL7 = False
 End if

End Function

‘******************************************************************************
‘ Name:  Is64Bit
‘
‘ Purpose: Checks if a Server is 64Bit
‘
‘ Parameters: sInstance, the name of the instance to check
‘
‘ Returns: True if 64Bit
‘   False if not 64bit
Public Function Is64Bit(sInstance)

  Dim sKeyValue
 
  sKeyValue = ReadRegistryStringValue(GetSQLInstanceKey(SQL_DEFAULT, “Setup”), “FeatureName”, ScriptContext.TargetComputer)
 
  If sKeyValue = “SQL_Engine” Then
    Is64Bit = True
    Call g_oUtil.LogMessage(g_oUtil.DBG_TRACE, “64Bit True for: ” & sInstance)
  Else
    Is64Bit = False
  End If

End Function

‘******************************************************************************
‘ Name:  IsMSDE
‘
‘ Purpose: Checks if a Server is MSDE
‘
‘ Parameters: sInstance, the name of the instance to check
‘
‘ Returns: True if Microsoft MSDE
‘   False if Microsoft MSDE is not installed
Public Function IsMSDE(sInstance)

  Dim sKeyValue
 
  if True = Is64Bit(sInstance) then
 IsMSDE = False
 Exit Function
  End if
 
  sKeyValue = ReadRegistryStringValue(GetSQLInstanceKey(sInstance, “Setup”), “ProductCode”, ScriptContext.TargetComputer)
 
  If Not IsNull(sKeyValue) Then
    IsMSDE = True
    Call g_oUtil.LogMessage(g_oUtil.DBG_TRACE, “MSDE True for: ” & sInstance)
  Else
    IsMSDE = False
  End If

End Function

‘******************************************************************************
‘ Name:  IsClustered
‘
‘ Purpose: Checks if an instance is clustered
‘
‘ Parameters: sInstance, the name of the instance to check
‘
‘ Returns: 1 if the instance is clustered
‘  0 if the instance is not clustered
‘

Public Function IsClustered(sInstance)

  Dim sClusterName
 
  sClusterName = ReadRegistryStringValue(GetSQLInstanceKey(sInstance, “Cluster”), “ClusterName”, ScriptContext.TargetComputer)
  If Not IsNull(sClusterName) Then
    IsClustered = 1
    Call g_oUtil.LogMessage(g_oUtil.DBG_TRACE, “The following node is a member of a cluster: ” & sInstance)
  Else
    IsClustered = 0
  End If
 
End Function

‘******************************************************************************
‘ Name:  IsSQLServiceStarted
‘
‘ Purpose: Checks whether a given SQL service instance is running  
‘
‘ Parameters: sInstance, the name of the SQL instance to check
‘
‘ Returns: 0 if the service is not started and not disabled
‘   1 if the service is started
‘   -1 if the service is not started but the service is disabled
‘   -2 if there was a WMI Error getting the service status or the
‘    service does not exist
‘

Public Function IsSQLServiceStarted(sInstance)
    IsSQLServiceStarted = IsServiceStarted(GetSQLServiceName(sInstance))
End Function

‘******************************************************************************
‘ Name:  IsServiceStarted
‘
‘ Purpose: Checks whether a given Windows service is running  
‘
‘ Parameters: sServiceName, the Windows service name (short)
‘
‘ Returns: 0 if the service is not started and not disabled
‘   1 if the service is started
‘   -1 if the service is not started but the service is disabled
‘   -2 if there was a WMI Error getting the service status or the
‘    service does not exist
‘

Public Function IsServiceStarted(sServiceName)

  Dim oService, sObjectString
 
  sObjectString = “winmgmts:\” & ScriptContext.TargetComputer & “rootcimv2”
   
  On Error Resume Next
  Err.Clear
  ‘We want to do our own error handling here. No WMIGetObject().
  Set oService = GetObject(sObjectString & “:Win32_Service.Name='” & sServiceName & “‘”)
  If Err.number <> 0 Then
    IsServiceStarted = -2
  Else
    If oService.State = “Running” Then
      IsServiceStarted = 1
    ElseIf oService.StartMode = “Disabled” Then
      IsServiceStarted = -1
    Else
      IsServiceStarted = 0
    End If
  End If
  Set oService = nothing
  On Error Goto 0
 
End Function

‘******************************************************************************
‘ Name:  GetSQLInstanceNameFromServiceName
‘
‘ Purpose: 
‘
‘ Parameters: 
‘
‘ Returns: 
‘
Public Function GetSQLInstanceNameFromServiceName(ByVal sServiceName)
    If sServiceName = SQL_DEFAULT Then
        GetSQLInstanceNameFromServiceName = SQL_DEFAULT
    Else
        GetSQLInstanceNameFromServiceName = Mid(sServiceName, 7)
    End If
End Function

‘******************************************************************************
‘ Name:  GetSQLServiceName
‘
‘ Purpose: 
‘
‘ Parameters: sInstance, the name of the instance to return the service name for
‘
‘ Returns: The service name
‘

Public Function GetSQLServiceName(sInstance)
    If sInstance = SQL_DEFAULT Then
        GetSQLServiceName = SQL_DEFAULT
    Else
        GetSQLServiceName = “MSSQL$” & sInstance
    End If
End Function

‘******************************************************************************
‘ Name:  GetSQLName
‘
‘ Purpose: Returns the name of the SQL server.  This will be the physical
‘  name of the server for a non clustered instance.  It will be the
‘  Virtual name for a clustered instance
‘
‘ Parameters: sInstance, the name of the instance to return the name for
‘
‘ Returns: The server name (virtual or physical)
‘  If there was an error getting the name “” is returned
‘

Public Function GetSQLName(sInstance)
  Dim sClusterName

  If IsClustered(sInstance) Then
    sClusterName = ReadRegistryStringValue(GetSQLInstanceKey(sInstance, “Cluster”), “ClusterName”, ScriptContext.TargetComputer)
   
    If Not IsNull(sClusterName) Then
      GetSQLName = sClusterName
    Else
      GetSQLName = “”
    End If
 
  Else
    GetSQLName = ScriptContext.TargetComputer   
  End If

Call g_oUtil.LogMessage(g_oUtil.DBG_TRACE, “GetSQLName returns: ” & GetSQLName)

End Function

‘******************************************************************************
‘ Name:  GetSQLInstanceName
‘
‘ Purpose: Returns the SQL Server instance name or default
‘
‘ Parameters: sName – The physical name of the SQL Server
‘    sInstance – The SQL Server instance name    
‘
‘ Returns: The default or named instance connection name
‘
Public Function GetSQLInstanceName(sName, sInstance)

    If sInstance = SQL_DEFAULT Then
      GetSQLInstanceName = sName
    Else
      GetSQLInstanceName = sName & “” & sInstance
    End If

End Function

‘******************************************************************************
‘ Name:  SQLConnect
‘
‘ Purpose: Creates a SQL DMO connection to an instance and runs a query
‘  at the instance level
‘
‘ Parameters: sInstance, the name of the instance to check
‘  
‘ Returns: 1 if a connection is successfully made and the query run
‘   0 if a connection cannot be made or the query cannot be run
‘
Public Function SQLConnect(sInstance)

  dim oSQLServer, sPath, oResults, sName

  On Error Resume Next

  sName = GetSQLName (sInstance)
  If sName <> “” Then

    Set oSQLServer = MomCreateObject(“SQLDMO.SQLServer”)
    If Err.number = 0 Then
      oSQLServer.LoginSecure = 1

   sPath = GetSQLInstanceName(sName, sInstance)

      oSQLServer.Connect sPath
      If Err.Number = 0 Then
        Set oResults = oSQLServer.ExecuteWithResults(“SELECT GETDATE()”)
        If Err.Number =  0 Then
          If oResults.GetColumnString(1,1) <> “” Then
            SQLConnect = 1
          End If
        Else
          SQLConnect = 0
        End If
      Else
        SQLConnect = 0
      End If
      oSQLServer.Disconnect
      Set oSQLServer = Nothing
      Set oResults = nothing
    Else
      SQLConnect = 0
    End If
  Else
    SQLConnect = 0
  End If

  On Error Goto 0
 
End Function

‘******************************************************************************
‘ Name:  CreateEvent
‘
‘ Purpose: Creates an event and inserts it into the MOM data stream
‘  The event will be created with the source set to the script name
‘
‘ Parameters: iEventID, the event ID
‘  iEventType, the event type (see constant values at top of file)
‘  sMessage, the message text for the event
‘

Public Sub CreateEvent(iEventID, iEventType, sMessage)
  On Error Resume Next

  Dim oNewEvent

  ‘ Create a new event
  Set oNewEvent = ScriptContext.CreateEvent()

  ‘ Set event properties
  oNewEvent.Message = sMessage
  oNewEvent.EventNumber = iEventID
  oNewEvent.EventType = iEventType

  ‘ Submit the event
  ScriptContext.Submit oNewEvent

  Set oNewEvent = Nothing

End Sub

”******************************************************************************
‘ Name:  CreateAlert
‘
‘ Purpose: Raises an alert
‘
‘ Parameters:
‘ iSeverity – The severity of the alert.
‘ strName – The name of the alert.
‘ strDescription – The alert description.
‘ strInstance – The instance of SQL for which to issue an alert.
‘   strComponent – The SQL component affected
‘
Function CreateAlert(iSeverity, strName, strDescription, strInstance, strComponent)
 Dim oAlert
 
 Set oAlert = ScriptContext.CreateAlert()
 
 oAlert.Name = strName
 oAlert.Description = strDescription
 oAlert.AlertSource = “SQL Server 2000 Replication Monitoring”
 oAlert.AlertLevel = iSeverity
 
 If strInstance <> “” And strComponent <> “” Then
     oAlert.ProblemState = PROBLEMSTATE_RED
     oAlert.ServerRole = SQL_CLASS_ID
        oAlert.ServerRoleInstance = strInstance
        oAlert.Component = strComponent
        oAlert.ComponentInstance = “”
    End If

 ScriptContext.Submit oAlert
End Function

‘******************************************************************************
‘ Name:  CollectDatabaseExclusions
‘
‘ Purpose: Collect the list of excluded databases
‘
‘ Parameters: None
‘
‘ Returns: None
‘
Sub CollectDatabaseExclusions()

 Dim oExcludeFile
 Dim oFSO
 Dim sExcludeDatabaseName
 
 On Error Resume Next
 
 Set oFSO = MomCreateObject(“Scripting.FileSystemObject”)
 Set oExcludeFile = oFSO.OpenTextFile(DATABASE_EXCLUDE_DIRECTORY & DATABASE_EXCLUDE_FILENAME, 1)
 
 If Err.number = 0 Then
        Call g_oUtil.LogMessage(g_oUtil.DBG_TRACE, “Exclusions are defined for this server”)
  g_bDatabaseExclusion = True
  sExcludeDatabaseName = oExcludeFile.ReadAll
  g_aDatabaseExcludeArray = Split(sExcludeDatabaseName, vbcrlf)
  oExcludeFile.Close
 Else
  g_bDatabaseExclusion = False
 End If

    On Error Goto 0 
End Sub

‘******************************************************************************
‘ Name:  IsDatabaseExcluded
‘
‘ Purpose: Determine if a specified database is exlcuded from monitoring
‘
‘ Parameters: Instance name and Database name
‘
‘ Returns: True or False
‘
Function IsDatabaseExcluded(sInstanceName, sDatabaseName)

 Dim sInstanceDatabaseName
 Dim iArrayMembers
 
 If g_bDatabaseExclusion Then
  If sInstanceName = SQL_DEFAULT Then
   sInstanceDatabaseName = sDatabaseName
  Else
   sInstanceDatabaseName = sInstanceName & “” & sDatabaseName
  End If

  For iArrayMembers = 0 To UBound(g_aDatabaseExcludeArray)

   If UCase(g_aDatabaseExcludeArray(iArrayMembers)) = UCase(sInstanceDatabaseName) Then
    IsDatabaseExcluded = True
            Call g_oUtil.LogMessage(g_oUtil.DBG_TRACE, “Database ” & sDatabaseName & “on instance ” & sInstanceName & ” is excluded”)
    Exit Function
   End If 
  Next
  
  IsDatabaseExcluded = False
 Else
  IsDatabaseExcluded = False
 End If

End Function

‘******************************************************************************
‘ Name:  CollectJobExclusions
‘
‘ Purpose: Collect the list of excluded servers/jobs
‘
‘ Parameters: None
‘
‘ Returns: None
‘
Sub CollectJobExclusions()

 Dim oExcludeFile
 Dim oFSO
 Dim sExcludeServerName
 
 On Error Resume Next
 
 Set oFSO = MomCreateObject(“Scripting.FileSystemObject”)
 Set oExcludeFile = oFSO.OpenTextFile(JOB_EXCLUDE_DIRECTORY & JOB_EXCLUDE_FILENAME, 1)
 
 If Err.number = 0 Then
        Call g_oUtil.LogMessage(g_oUtil.DBG_TRACE, “Exclusions are defined for this server”)
  g_bJobExclusion = True
  sExcludeServerName = oExcludeFile.ReadAll
  g_aJobExcludeArray = Split(sExcludeServerName, vbcrlf)
  oExcludeFile.Close
 Else
  g_bJobExclusion = False
 End If

    On Error Goto 0 
End Sub

‘******************************************************************************
‘ Name:  IsJobExcluded
‘
‘ Purpose: Determine if a specified server/job is exlcuded from monitoring
‘
‘ Parameters: Server name and Job name
‘
‘ Returns: True or False
‘
Function IsJobExcluded(sServerName, sJobName)
 Dim sServerJobName
 Dim iArrayMembers
 
 If g_bJobExclusion Then
  sServerJobName = sServerName & “” & sJobName

  For iArrayMembers = 0 To UBound(g_aJobExcludeArray)

   If UCase(g_aJobExcludeArray(iArrayMembers)) = UCase(sServerJobName) Then
    IsJobExcluded = True
            Call g_oUtil.LogMessage(g_oUtil.DBG_TRACE, “Job ” & sJobName & “on server ” & sServerName & ” is excluded”)
    Exit Function
   End If 
  Next
  
  IsJobExcluded = False
 Else
  IsJobExcluded = False
 End If

End Function

‘******************************************************************************
‘ Name:  ServiceStateUpdate
‘
‘ Purpose: Generate State Monitoring Red and Green lights for Summary State View
‘
‘ Parameters: sStateToUpdate – eg Agent, Server, Connectivity
‘    sServiceName – name of the instance eg Instance1, Instance2
‘    sStateDescription – text to be included in the alert description
‘    sHealthState – defined by constants
‘
‘ Returns: Nothing
‘

Public Function ServiceStateUpdate(sStateToUpdate, sServiceName, sStateSource, sStateDescription, iHealthState)

 Dim lngProblemState
 Dim oAlert
 Dim iProblemState
 Dim iAlertSev

 Set oAlert = ScriptContext.CreateAlert()
 
 oAlert.Description = sStateDescription
 oAlert.AlertSource =  sStateSource
 
 oAlert.ServerRole = SQL_CLASS_ID
 oAlert.ServerRoleInstance = sServiceName

 Select Case sStateToUpdate
  Case “Server”
   oAlert.Name = “SQL Server 2000 Service Availability”
   oAlert.Component = SQL_SQL_COMPONENT_ID
  Case “Agent”
   oAlert.Name = “SQL Server Agent Service Availability”
   oAlert.Component = SQL_AGENT_COMPONENT_ID
  Case “Connectivity”
   oAlert.Name = “SQL Server Local Connectivity”
   oAlert.Component = SQL_CONNECTIVITY_COMPONENT_ID
  Case “DatabaseHealth”
   oAlert.Name = “SQL Server 2000 Database Health”
   oAlert.Component = SQL_DB_HEALTH_COMPONENT_ID
  Case “DatabaseSpace”
   oAlert.Name = “SQL Server Database Space”
   oAlert.Component = SQL_DB_SPACE_COMPONENT_ID
  Case “LogSpace”
   oAlert.Name = “SQL Server Log Space”
   oAlert.Component = SQL_LOG_SPACE_COMPONENT_ID
 End Select
 
 Select Case iHealthState
  Case SERVICESTATE_GREEN
   iProblemState = PROBLEMSTATE_GREEN
   iAlertSev = ALERT_CRITICAL_ERROR
  Case SERVICESTATE_YELLOW
   iProblemState = PROBLEMSTATE_RED
   iAlertSev = ALERT_WARNING
  Case SERVICESTATE_RED
   iProblemState = PROBLEMSTATE_RED
   iAlertSev = ALERT_CRITICAL_ERROR
 End Select

 oAlert.ProblemState = iProblemState
 oAlert.AlertLevel = iAlertSev
 ScriptContext.Submit oAlert 

End Function

‘******************************************************************************
‘ Name:  ReadRegistryStringValue
‘
‘ Purpose: Return a string value from the registry (HKLM)
‘
‘ Parameters: sKeyPath, the path to the key
‘    sValueName, the name of the value to return
‘    sHostName, the computer to connect to
‘
‘ Returns: A string matching the contents of the value or null
‘
Public Function ReadRegistryStringValue(sKeyPath, sValueName, sHostName)
    Dim lResult
    ReadRegistryStringValue = m_oSafeRegistry.ReadStringValue(sKeyPath, sValueName, sHostName, lResult)
End Function

‘******************************************************************************
‘ Name:  ReadRegistryMultiStringValue
‘
‘ Purpose: Return a multi string value from the registry (HKLM)
‘
‘ Parameters: sKeyPath, the path to the key
‘    sValueName, the name of the value to return
‘    sHostName, the computer to connect to
‘
‘ Returns: A string matching the contents of the value or null
‘
Public Function ReadRegistryMultiStringValue(sKeyPath, sValueName, sHostName)
    Dim lResult
    ReadRegistryMultiStringValue = m_oSafeRegistry.ReadMultiStringValue(sKeyPath, sValueName, sHostName, lResult)
End Function

‘******************************************************************************
‘ Name:  ReadRegistryDWORDValue
‘
‘ Purpose: Return a DWORD value from the registry (HKLM)
‘
‘ Parameters: sKeyPath, the path to the key
‘    sValueName, the name of the value to return
‘    sHostName, the computer to connect to
‘
‘ Returns: A string matching the contents of the value or null
‘
Public Function ReadRegistryDWORDValue(sKeyPath, sValueName, sHostName)
    Dim lResult
    ReadRegistryDWORDValue = m_oSafeRegistry.ReadDWORDValue(sKeyPath, sValueName, sHostName, lResult)
End Function

End Class

Class SafeRegistry
    Private m_oError
    Private m_oRegistry
    Private m_sHive
    Public SUPPRESS_KEY_NOT_FOUND
    Public SUPPRESS_VALUE_NOT_FOUND
    Public SUPPRESS_ACCESS_DENIED
    Public SUPPRESS_ALL
    Private m_lSuppressionFlags
   
    Private Sub Class_Initialize()
        Set m_oError = New Error
        m_sHive = “HKLM”
        Set m_oRegistry = New Registry
        SUPPRESS_KEY_NOT_FOUND = &H00000001
        SUPPRESS_VALUE_NOT_FOUND = &H00000002
        SUPPRESS_ACCESS_DENIED = &H00000004
        SUPPRESS_ALL = &HFFFFFFFF
        m_lSuppressionFlags = 0
    End Sub
   
    Public Property Let SuppressionFlags(ByVal lValue)
        m_lSuppressionFlags = lValue
    End Property
   
    Public Property Get SuppressionFlags()
        SuppressionFlags = m_lSuppressionFlags
    End Property
   
    Public Function ReadDWORDValue(ByVal sKeyPath, ByVal sValueName, ByVal sHostName, ByRef lResult)
        ReadDWORDValue = Null
       
        On Error Resume Next
        ReadDWORDValue = m_oRegistry.ReadDWORDValue(sKeyPath, sValueName, sHostName, lResult)
        m_oError.Save
        On Error Goto 0
       
        If m_oError.Number <> 0 Then
            ThrowScriptErrorNoAbort GET_ERROR_READING_REGISTRY_MESSAGE(m_sHive, sKeyPath, sValueName), m_oError
            Exit Function
        End If
       
        HandleResult m_sHive, sKeyPath, sValueName, lResult
    End Function
   
    Public Function ReadStringValue(ByVal sKeyPath, ByVal sValueName, ByVal sHostName, ByRef lResult)
        ReadStringValue = Null
       
        On Error Resume Next
        ReadStringValue = m_oRegistry.ReadStringValue(sKeyPath, sValueName, sHostName, lResult)
        m_oError.Save
        On Error Goto 0
       
        If m_oError.Number <> 0 Then
            ThrowScriptErrorNoAbort GET_ERROR_READING_REGISTRY_MESSAGE(m_sHive, sKeyPath, sValueName), m_oError
            Exit Function
        End If

        HandleResult m_sHive, sKeyPath, sValueName, lResult
    End Function

    Public Function ReadMultiStringValue(ByVal sKeyPath, ByVal sValueName, ByVal sHostName, ByRef lResult)
        ReadMultiStringValue = Null
       
        On Error Resume Next
        ReadMultiStringValue = m_oRegistry.ReadMultiStringValue(sKeyPath, sValueName, sHostName, lResult)
        m_oError.Save
        On Error Goto 0
       
        If m_oError.Number <> 0 Then
            ThrowScriptErrorNoAbort GET_ERROR_READING_REGISTRY_MESSAGE(m_sHive, sKeyPath, sValueName), m_oError
            Exit Function
        End If

        HandleResult m_sHive, sKeyPath, sValueName, lResult
    End Function
   
    Public Function EnumKeys(ByVal sKeyPath, ByVal sHostName, ByRef lResult)
        EnumKeys = Null
       
        On Error Resume Next
        EnumKeys = m_oRegistry.EnumKeys(sKeyPath, sHostName, lResult)
        m_oError.Save
        On Error Goto 0
       
        If m_oError.Number <> 0 Then
            ThrowScriptErrorNoAbort GET_ERROR_READING_KEY_MESSAGE(m_sHive, sKeyPath), m_oError
            Exit Function
        End If
       
        HandleResult m_sHive, sKeyPath, “”, lResult
    End Function
   
    Private Sub HandleResult(ByVal sHive, ByVal sKeyPath, ByVal sValueName, ByVal lResult)
        Select Case lResult
            Case m_oRegistry.SUCCESS
                Exit Sub
            Case m_oRegistry.ERROR_ACCESS_DENIED
                If (SuppressionFlags And SUPPRESS_ACCESS_DENIED) = 0 Then
                    ThrowScriptErrorNoAbort GET_REGISTRY_ACCESS_DENIED_MESSAGE(sHive, sKeyPath, sValueName), Err
                Else
                    ScriptContext.Echo GET_REGISTRY_ACCESS_DENIED_MESSAGE(sHive, sKeyPath, sValueName)
                End If
            Case m_oRegistry.ERROR_VALUE_NOT_FOUND
                If (SuppressionFlags And SUPPRESS_VALUE_NOT_FOUND) = 0 Then
                    ThrowScriptErrorNoAbort GET_REGISTRY_VALUE_NOT_FOUND_MESSAGE(sHive, sKeyPath, sValueName), Err
                Else
                    ScriptContext.Echo GET_REGISTRY_VALUE_NOT_FOUND_MESSAGE(sHive, sKeyPath, sValueName)
                End If
            Case m_oRegistry.ERROR_KEY_NOT_FOUND
                If (SuppressionFlags And SUPPRESS_KEY_NOT_FOUND) = 0 Then
                    ThrowScriptErrorNoAbort GET_REGISTRY_KEY_NOT_FOUND_MESSAGE(sHive, sKeyPath), Err
                Else
                    ScriptContext.Echo GET_REGISTRY_KEY_NOT_FOUND_MESSAGE(sHive, sKeyPath)
                End If
            Case Else
                If (SuppressionFlags And SUPPRESS_ALL) = 0 Then
                    ThrowScriptErrorNoAbort GET_ERROR_READING_REGISTRY_MESSAGE(sHive, sKeyPath, sValueName), Err
                Else
                    ScriptContext.Echo GET_ERROR_READING_REGISTRY_MESSAGE(sHive, sKeyPath, sValueName)
                End If
        End Select
    End Sub
   
    Private Function GET_REGISTRY_ACCESS_DENIED_MESSAGE(ByVal sHive, ByVal sRegKey, ByVal sRegValue)
        Const REGISTRY_ACCESS_DENIED_MESSAGE = “Access denied while reading registry value [{Hive}{RegKey}{RegValue}]”
        Dim sResult
        sResult = Replace(REGISTRY_ACCESS_DENIED_MESSAGE, “{RegKey}”, sRegKey)
        sResult = Replace(sResult, “{Hive}”, sHive)
        GET_REGISTRY_ACCESS_DENIED_MESSAGE = Replace(sResult, “{RegValue}”, sRegValue)
    End Function

    Private Function GET_REGISTRY_VALUE_NOT_FOUND_MESSAGE(ByVal sHive, ByVal sRegKey, ByVal sRegValue)
        Const REGISTRY_VALUE_NOT_FOUND_MESSAGE = “Registry value [{Hive}{RegKey}{RegValue}] not found”
        Dim sResult
        sResult = Replace(REGISTRY_VALUE_NOT_FOUND_MESSAGE, “{RegKey}”, sRegKey)
        sResult = Replace(sResult, “{Hive}”, sHive)
        GET_REGISTRY_VALUE_NOT_FOUND_MESSAGE = Replace(sResult, “{RegValue}”, sRegValue)
    End Function

    Private Function GET_ERROR_READING_REGISTRY_MESSAGE(ByVal sHive, ByVal sRegKey, ByVal sRegValue)
        Const ERROR_READING_REGISTRY_MESSAGE = “Error while reading registry value [{Hive}{RegKey}{RegValue}]”
        Dim sResult
        sResult = Replace(ERROR_READING_REGISTRY_MESSAGE, “{RegKey}”, sRegKey)       
        sResult = Replace(sResult, “{Hive}”, sHive)
        GET_ERROR_READING_REGISTRY_MESSAGE = Replace(sResult, “{RegValue}”, sRegValue)
    End Function

    Function GET_REGISTRY_KEY_NOT_FOUND_MESSAGE(ByVal sHive, ByVal sRegKey)
        Const REGISTRY_KEY_NOT_FOUND_MESSAGE = “Registry key [{Hive}{RegKey}] not found”
        Dim sResult
        sResult = Replace(REGISTRY_KEY_NOT_FOUND_MESSAGE, “{Hive}”, sHive)
        GET_REGISTRY_KEY_NOT_FOUND_MESSAGE = Replace(sResult, “{RegKey}”, sRegKey)
    End Function

    Function GET_ERROR_READING_KEY_MESSAGE(ByVal sHive, ByVal sRegKey)
        Const ERROR_READING_KEY_MESSAGE = “Error while reading registry key [{Hive}{RegKey}]”
        Dim sResult
        sResult = Replace(ERROR_READING_KEY_MESSAGE, “{Hive}”, sHive)
        GET_ERROR_READING_KEY_MESSAGE = Replace(sResult, “{RegKey}”, sRegKey)
    End Function
End Class

Class Registry
    Private HKEY_CLASSES_ROOT
    Private HKEY_CURRENT_USER
    Private HKEY_LOCAL_MACHINE
    Private HKEY_USERS
    Private HKEY_CURRENT_CONFIG
    Private HKEY_DYN_DATA

    Public ERROR_ACCESS_DENIED
    Public ERROR_KEY_NOT_FOUND
    Public ERROR_VALUE_NOT_FOUND
    Public SUCCESS
   
    Private Sub Class_Initialize()
        HKEY_CLASSES_ROOT = &H80000000
        HKEY_CURRENT_USER = &H80000001
        HKEY_LOCAL_MACHINE = &H80000002
        HKEY_USERS = &H80000003
        HKEY_CURRENT_CONFIG = &H80000005
        HKEY_DYN_DATA = &H80000006
       
        ERROR_ACCESS_DENIED = 5
        ERROR_KEY_NOT_FOUND = 2
        ERROR_VALUE_NOT_FOUND = 1
        SUCCESS = 0
    End Sub
   
    Public Function ReadDWORDValue(ByVal sKeyPath, ByVal sValueName, ByVal sHostName, ByRef lResult)
        Dim oReg
        Set oReg = WMIGetObject(“winmgmts://” & sHostName & “/root/default:StdRegProv”)
        Dim lValue
        lResult = oReg.GetDWORDValue(HKEY_LOCAL_MACHINE, sKeyPath, sValueName, lValue)
        ReadDWORDValue = lValue
    End Function
   
    Public Function ReadStringValue(ByVal sKeyPath, ByVal sValueName, ByVal sHostName, ByRef lResult)
        Dim oReg
        Set oReg = WMIGetObject(“winmgmts://” & sHostName & “/root/default:StdRegProv”)
        Dim sValue
        lResult = oReg.GetStringValue(HKEY_LOCAL_MACHINE, sKeyPath, sValueName, sValue)
        ReadStringValue = sValue
    End Function
   
    Public Function ReadMultiStringValue(ByVal sKeyPath, ByVal sValueName, ByVal sHostName, ByRef lResult)
        Dim oReg
        Set oReg = WMIGetObject(“winmgmts://” & sHostName & “/root/default:StdRegProv”)
        Dim aValues
        lResult = oReg.GetMultiStringValue(HKEY_LOCAL_MACHINE, sKeyPath, sValueName, aValues)
        ReadMultiStringValue = aValues
    End Function
   
    Public Function EnumKeys(ByVal sKeyPath, ByVal sHostName, ByRef lResult)
        Dim oReg
        Set oReg = WMIGetObject(“winmgmts://” & sHostName & “/root/default:StdRegProv”)
        Dim aSubKeys
        lResult = oReg.EnumKey(HKEY_LOCAL_MACHINE, sKeyPath, aSubKeys)
        EnumKeys = aSubKeys
    End Function
End Class

Class Error
    Private m_lNumber
    Private m_sSource
    Private m_sDescription
    Private m_sHelpContext
    Private m_sHelpFile
    Public Sub Save()
        m_lNumber = Err.number
        m_sSource = Err.Source
        m_sDescription = Err.Description
        m_sHelpContext = Err.HelpContext
        m_sHelpFile = Err.helpfile
    End Sub
    Public Sub Raise()
        Err.Raise m_lNumber, m_sSource, m_sDescription, m_sHelpFile, m_sHelpContext
    End Sub
    Public Sub Clear()
        m_lNumber = 0
        m_sSource = “”
        m_sDescription = “”
        m_sHelpContext = “”
        m_sHelpFile = “”
    End Sub
    Public Property Get Number()
        Number = m_lNumber
    End Property
    Public Property Get Source()
        Source = m_sSource
    End Property
    Public Property Get Description()
        Description = m_sDescription
    End Property
    Public Property Get HelpContext()
        HelpContext = m_sHelpContext
    End Property
    Public Property Get HelpFile()
        HelpFile = m_sHelpFile
    End Property   
End Class

Dim g_oUtil
Set g_oUtil = New Util

‘==========================================================================
‘ Class:        Util
‘ Description:  Utility methods for logging, creating MOM alert
‘==========================================================================
Class Util

 ’ Used to say to LogMessage when/how to print the message.
 Public DBG_NONE
 Public DBG_ERROR
 Public DBG_WARNING
 Public DBG_TRACE
 Public HKEY_LOCAL_MACHINE

 ’Internal Debug Level
 Private m_nDebugLevel  
    Private m_oSafeRegistry
 ’—————
 ’ Methods
 ’—————

 ’=============
 ’ Method:       Class_Initialize
 ’ Description:  This is the constructor
 ’ Parameters:
 ’=============
 Private Sub Class_Initialize()
  ’ Initialize Debug level constants
  DBG_TRACE = 1
  DBG_WARNING = 2
  DBG_ERROR = 3
  DBG_NONE = 4

  ’by default only errors are logged
  m_nDebugLevel = DBG_ERROR
  
  Set m_oSafeRegistry = New SafeRegistry
  m_oSafeRegistry.SuppressionFlags = (m_oSafeRegistry.SUPPRESS_KEY_NOT_FOUND Or m_oSafeRegistry.SUPPRESS_VALUE_NOT_FOUND)

 End Sub
 
 ’=============
 ’ Method:       Class_Terminate
 ’ Description:  This is the destructor
 ’ Parameters:
 ’=============
 Private Sub Class_Terminate()
 End Sub

 ’=============
 ’ Method:       SetDebugLevel
 ’ Description:  To change the debugging output level of information
 ’               generated by this utility.
 ’ Parameters:
 ’               nLevel – Level, either DBG_NONE, DBG_TRACE,
 ’                        DBG_WARNING or DBG_ERROR
 ’=============
 Public Sub SetDebugLevel(ByVal nLevel)
   m_nDebugLevel = nLevel
 End Sub
 
 ’=============
 ’ Method:       LogMessage
 ’ Description:  Log a debug message to ScriptContext
 ’ Parameters:
 ’               nLevel      – Debug level for the message that we’re logging.
 ’               strMessage  – The message to write to the trace.
 ’=============
 Public Sub LogMessage( _
  ByVal nLevel, _
  ByVal strMessage _
  )
  If (nLevel >= m_nDebugLevel) Then
   if (nLevel = DBG_ERROR) Then
    ScriptContext.Echo “[Error]: ” & strMessage
   ElseIf (nLevel = DBG_WARNING) Then
    ScriptContext.Echo “[Warning]: ” & strMessage
   ElseIf (nLevel = DBG_TRACE) Then
    ScriptContext.Echo “[Trace]:” & strMessage
   End If
  End If
 End Sub
 
 ’=============
 ’ Method:       SplitVerStr
 ’ Description:  Split a version string into integers.
 ’ Parameters:
 ’               strVer           – The version string.
 ’               iMajor           – The output integer for major version.
 ’               iMinor           – The output integer for minor version.
 ’=============
 Function SplitVerStr(ByVal strVer, ByRef iMajor, ByRef iMinor)
  Dim iPos
  Dim strMinor
  
  iPos = InStr(strVer, “.”)
  
  if 0 = iPos then
   iMajor = CInt(strVer)
   iMinor = 0
   Exit Function
  end if
  
  iMajor = CInt(Left(strVer, iPos))
  strMinor = Right(strVer, Len(strVer)-iPos)
  
  iPos = InStr(strMinor, “.”)
  
  if 0 = iPos then
   iMinor = CInt(strMinor)
  else
   iMinor = CInt(Left(strMinor, iPos))
  end if

 End Function

 ’=============
 ’ Method:       ReadRegistryValue
 ’ Description:  Used to read strings from the registry
 ’ Parameters:
 ’               Root  - Root of the registry (HKEY_LOCAL_MACHINE, HKEY_USERS etc. Refer to constants defined earlier)
 ’               strKeyPath - Key path for the Registry key to read
 ’                             (like “SOFTWAREMicrosoftWindowsNTCurrentVersion”)
 ’               strValueName – Name of the registry entry to read (like “SoftwareType”)
 ’    
 ’ Returns:
 ’    The value of the registry key specified. “Nothing” if it fails. Callee needs to handle null value return.
 ’=============
 Public Function ReadRegistryValue(strKeyPath, strValueName, strHostName)
     Dim lResult
     Dim strValueData
     strValueData = m_oSafeRegistry.ReadStringValue(strKeyPath, strValueName, strHostName, lResult)
    
  If Not IsNull(strValueData) Then
   Call LogMessage(DBG_TRACE, “Value of Registry Key: ” & strKeyPath & “” & strValueName & ” = ” & strValueData)
   ReadRegistryValue = strValueData
  Else
   Call LogMessage(DBG_ERROR, “Reading Registry Key: ” & strKeyPath & “” & strValueName & ” Failed!” )
   ReadRegistryValue = empty
  End If
 End Function
 
End Class

Function IsValidObject(ByVal oObject)
    IsValidObject = False
   
    If IsObject(oObject) Then
        If Not oObject Is Nothing Then
            IsValidObject = True
        End If
    End If
End Function 

Function WMIGetObject(sNamespace)
‘
‘ WMIGetObject :: Returns the WMI object requested.
‘
‘
 Dim oWMI

 On Error Resume Next
 Set oWMI = GetObject(sNamespace)
 If IsEmpty(oWMI) Then
  ThrowScriptError “Unable to open WMI Namespace ‘” & sNamespace & “‘.  Check to see if the WMI service is enabled and running, and ensure this WMI namespace exists.”, Err
 End If
 On Error Goto 0

 Set WMIGetObject = oWMI
 Set oWMI = Nothing

End Function

Function WMIGetInstance(sNamespace, sInstance)
‘
‘ WMIGetInstance :: Returns WMI Instance requested.
‘
‘
 Dim oWMI, oInstance, nInstanceCount

 Set oInstance = oWMI.InstancesOf(sInstance)
 If IsEmpty(oInstance) Or Err.Number <> 0 Then
  ThrowScriptError “The class name ‘” & sInstance & “‘ returned no instances.  Please check to see if this is a valid WMI class name.”, Err
 End If

 ’Determine if we queried a valid WMI class – Count will return 0 or empty

 nInstanceCount = oInstance.Count
 If Err.Number <> 0 Then
  ThrowScriptError “The class name ‘” & sInstance & “‘ did not return any valid instances.  Please check to see if this is a valid WMI class name.”, Err
 End If
 On Error Goto 0

 Set WMIGetInstance = oInstance

 Set oInstance = Nothing
 Set oWMI = Nothing

End Function

Function WMIExecQuery(sNamespace, sQuery)
‘
‘ WMIExecQuery :: Executes the WMI query and returns the result set.
‘
‘
 Dim oWMI, oQuery, nInstanceCount
 
 On Error Resume Next
 Set oWMI = GetObject(sNamespace)
 If IsEmpty(oWMI) Then
  ThrowScriptError “Unable to open WMI Namespace ‘” & sNamespace & “‘.  Check to see if the WMI service is enabled and running, and ensure this WMI namespace exists.”, Err
 End If

 Set oQuery = oWMI.ExecQuery(sQuery)
 If IsEmpty(oQuery) Or Err.Number <> 0 Then
  ThrowScriptError “The Query ‘” & sQuery & “‘ returned an invalid result set.  Please check to see if this is a valid WMI Query.”, Err
 End If

 ’Determine if we queried a valid WMI class – Count will return 0 or empty
 nInstanceCount = oQuery.Count
 If Err.Number <> 0 Then
  ThrowScriptError “The Query ‘” & sQuery & “‘ did not return any valid instances.  Please check to see if this is a valid WMI Query.”, Err
 End If
 On Error Goto 0

 Set WMIExecQuery = oQuery

 Set oQuery = Nothing
 Set oWMI = Nothing
End Function

Function WMIGetInstanceNoAbort(sNamespace, sInstance)
‘
‘ WMIGetInstanceNoAbort :: Returns WMI Instance requested.
‘
‘
 Dim oWMI, oInstance, nInstanceCount

 On Error Resume Next
 Set oWMI = GetObject(sNamespace)
 If Not IsEmpty(oWMI) Then

  Set oInstance = oWMI.InstancesOf(sInstance)
  If Not IsEmpty(oInstance) And Err.Number = 0 Then

   ’Determine if we queried a valid WMI class – Count will return 0 or empty
   nInstanceCount = oInstance.Count
   If Err.Number = 0 Then
    Set WMIGetInstanceNoAbort = oInstance
    Exit Function
   End If
  End If
 End If
 On Error Goto 0

 Set WMIGetInstanceNoAbort = Nothing

 Set oInstance = Nothing
 Set oWMI = Nothing

End Function

Const wbemCimtypeUseDefault = 0   ‘Use Default Type CIM type – Custom
Const wbemCimtypeSint16     = 2   ‘Signed 16-bit integer
Const wbemCimtypeSint32     = 3   ‘Signed 32-bit integer
Const wbemCimtypeReal32     = 4   ’32-bit real number
Const wbemCimtypeReal64     = 5   ’64-bit real number
Const wbemCimtypeString     = 8   ‘String
Const wbemCimtypeBoolean    = 11  ‘Boolean value
Const wbemCimtypeObject     = 13  ‘CIM object
Const wbemCimtypeSint8      = 16  ‘Signed 8-bit integer
Const wbemCimtypeUint8      = 17  ‘Unsigned 8-bit integer
Const wbemCimtypeUint16     = 18  ‘Unsigned 16-bit integer
Const wbemCimtypeUint32     = 19  ‘Unsigned 32-bit integer
Const wbemCimtypeSint64     = 20  ‘Signed 64-bit integer 
Const wbemCimtypeUint64     = 21  ‘Unsigned 64-bit integer
Const wbemCimtypeDatetime   = 101 ‘Date/time value
Const wbemCimtypeReference  = 102 ‘Reference to a CIM object
Const wbemCimtypeChar16     = 103 ’16-bit character

Const ErrAction_None = 0
Const ErrAction_Trace = 1
Const ErrAction_ThrowError = 16
Const ErrAction_Abort = 32
Const ErrAction_ThrowErrorAndAbort = 48

Function GetWMIProperty(oWmi, sPropName, nCIMType, ErrAction)
 Dim sValue, oWmiProp

 If Not IsObject(oWmi) Then
  If (ErrAction And ErrAction_ThrowError) = ErrAction_ThrowError Then _
   ThrowScriptErrorNoAbort “Accessing property on invalid WMI object.”, Err

  If (ErrAction And ErrAction_Abort) = ErrAction_Abort Then _
   ScriptContext.Quit()

  GetWMIProperty = “”
  Exit Function
 End If

 On Error Resume Next
 Set oWmiProp = oWmi.Properties_.Item(sPropName)
 If Err.Number <> 0 Then
  If (ErrAction And ErrAction_ThrowError) = ErrAction_ThrowError Then _
   ThrowScriptErrorNoAbort “An error occurred while accessing WMI property: ‘” & sPropName & “‘.”, Err

  If (ErrAction And ErrAction_Abort) = ErrAction_Abort Then _
   ScriptContext.Quit()
 End If
 On Error Goto 0
 
 If IsValidObject(oWmiProp) Then
  sValue = oWmiProp.Value

  If IsNull(sValue) Then
   ’
   ’ If value is null, return blank to avoid any issues
   ’
   GetWMIProperty = “”

  Else

   Select Case (oWmiProp.CIMType)
    Case wbemCimtypeString, wbemCimtypeSint16, wbemCimtypeSint32, wbemCimtypeReal32, wbemCimtypeReal64, wbemCimtypeSint8, wbemCimtypeUint8, wbemCimtypeUint16, wbemCimtypeUint32, wbemCimtypeSint64, wbemCimtypeUint64:
     GetWMIProperty = Trim(CStr(sValue))
    Case wbemCimtypeBoolean:
     If sValue = 1 Or UCase(sValue) = “TRUE” Then
      GetWMIProperty = “True”
     Else
      GetWMIProperty = “False”
     End If 
    Case wbemCimtypeDatetime:

     Dim sTmpStrDate
   
     ’
     ’ First attempt to convert the whole wmi date string
     ’
     sTmpStrDate = Mid(sValue, 5, 2) & “/” & _
       Mid(sValue, 7, 2) & “/” & _
       Left(sValue, 4) & ” ” & _
       Mid (sValue, 9, 2) & “:” & _
       Mid(sValue, 11, 2) & “:” & _
       Mid(sValue, 13, 2)
     If IsDate(sTmpStrDate) Then
      GetWMIProperty = CDate(sTmpStrDate)
     Else

      ’
      ’ Second, attempt just to convert the YYYYMMDD
      ’
      sTmpStrDate = Mid(sValue, 5, 2) & “/” & _
        Mid(sValue, 7, 2) & “/” & _
        Left(sValue, 4)
      If IsDate(sTmpStrDate) Then
       GetWMIProperty = CDate(sTmpStrDate)
      Else
       ’
       ’ Nothing works – return passed in string
       ’
       GetWMIProperty = sValue
      End If
  
     End If
    
    Case Else:
     GetWMIProperty = “”
   End Select
  End If
 Else

  If (ErrAction And ErrAction_ThrowError) = ErrAction_ThrowError Then _
   ThrowScriptErrorNoAbort “An error occurred while accessing WMI property: ‘” & sPropName & “‘.”, Err

  If (ErrAction And ErrAction_Abort) = ErrAction_Abort Then _
   ScriptContext.Quit()

  GetWMIProperty = “”

 End If

 If (ErrAction And ErrAction_Trace) = ErrAction_Trace Then _
  ScriptContext.Echo “  + ” & sPropName & ” :: ‘” & GetWMIProperty & “‘”
 
End Function

Const EVENT_TYPE_SUCCESS = 0
Const EVENT_TYPE_ERROR = 1
Const EVENT_TYPE_WARNING = 2
Const EVENT_TYPE_INFORMATION = 4
Const EVENT_TYPE_AUDITSUCCESS = 8
Const EVENT_TYPE_AUDITFAILURE = 16

Function ThrowScriptError(sMessage, oErr)
‘
‘ ThrowScriptError :: Creates an event and sends it back to the mom server
‘
‘
    On Error Resume Next
    ThrowScriptErrorNoAbort sMessage, oErr
 ScriptContext.Quit()
End Function

Function ThrowScriptErrorNoAbort(sMessage, oErr)
‘
‘ ThrowScriptError :: Creates an event and sends it back to the mom server
‘
‘

 Dim sErrDescription, sErrNumber
 sErrDescription = oErr.Description
 sErrNumber = oErr.Number

 On Error Resume Next

 Dim oScriptErrorEvent

 Set oScriptErrorEvent = ScriptContext.CreateEvent()
 With oScriptErrorEvent
  .EventNumber = 40000
  .EventType = EVENT_TYPE_ERROR
  .Message = sMessage
  .SetEventParameter “Microsoft SQL Server”
  .SetEventParameter sMessage
  .SetEventParameter sErrDescription
  .SetEventParameter sErrNumber
 End With
 ScriptContext.Submit oScriptErrorEvent
 ScriptContext.Echo “ThrowScriptError(‘” & sMessage & “‘)”
End Function

Function MomCreateObject(sProgramId)
‘
‘ MomCreateObject :: Creates an automation object.
‘
‘
 On Error Resume Next
 Set MomCreateObject = CreateObject(sProgramId)
 If Err.Number <> 0 Then
  ThrowScriptError “Unable to create automation object ‘” & sProgramId & “‘”, Err
 End If
 On Error Goto 0
End Function

Michel

2 comments

Please could you post a link to the source of this information, maybe the original Microsoft link, thanks.

Paul/ 17 years ago
Paul , Its is an original post. Anyway if you upgrade to the latest SQl MP the problem is sovled. Or you can copy and paste the code in my post. No changes are done so it’s save.

michel

michel [admin]/ 17 years ago

Comments are closed.