SQL 2005 DB State Provider

Microsoft.SQLServer.2005.DBStateProvider (DataSourceModuleType)

Element properties:

Member Modules:

Overrideable Parameters:

Source Code:

<DataSourceModuleType ID="Microsoft.SQLServer.2005.DBStateProvider" Accessibility="Internal" RunAs="SQL!Microsoft.SQLServer.SQLProbeAccount">

  <Configuration>

    <xsd:element name="IntervalSeconds" type="xsd:integer"/>

    <xsd:element name="SyncTime" type="xsd:string"/>

    <xsd:element name="ConnectionString" type="xsd:string"/>

    <xsd:element name="TimeoutSeconds" type="xsd:int"/>

  </Configuration>

  <OverrideableParameters>

    <OverrideableParameter ID="IntervalSeconds" ParameterType="int" Selector="$Config/IntervalSeconds$"/>

    <OverrideableParameter ID="SyncTime" ParameterType="string" Selector="$Config/SyncTime$"/>

    <OverrideableParameter ID="TimeoutSeconds" ParameterType="int" Selector="$Config/TimeoutSeconds$"/>

  </OverrideableParameters>

  <ModuleImplementation>

    <Composite>

      <MemberModules>

        <DataSource ID="DS" TypeID="Windows!Microsoft.Windows.TimedScript.PropertyBagProvider">

          <IntervalSeconds>$Config/IntervalSeconds$</IntervalSeconds>

          <SyncTime>$Config/SyncTime$</SyncTime>

          <ScriptName>GetSQL2005DB.vbs</ScriptName>

          <Arguments>$Config/ConnectionString$</Arguments>

          <ScriptBody><Script>

'Copyright (c) Microsoft Corporation. All rights reserved.

' This script takes a single parameter

' Param 0: The SQL connection string to connect to

' All database state is output in a property bag. A number of properties are output

' Name: DBName\State Value: Returns the status of the db. Status can be:

'   RESTORING

'   RECOVERING

'   RECOVERY_PENDING

'   SUSPECT

'   EMERGENCY

'   OFFLINE

'



Option Explicit

SetLocale("en-us")

Const SQL_DISCOVERY_CONNECT_FAILURE = -1

Const SQL_DISCOVERY_QUERY_FAILURE = -2

Const DB_PROPERTY = "Status"



Call GetDBHealth()



Sub GetDBHealth()

    

    if WScript.Arguments.Count = 1 Then



    	Dim oAPI, oBag

	Set oAPI = MOMCreateObject("MOM.ScriptAPI")

	Set oBag= oAPI.CreatePropertyBag()

	If DBHealth(WScript.Arguments(0), oBag) &gt;= 0 Then 

		Call oAPI.Return(oBag)

	End If



    else

        Wscript.quit()

    end if

End Sub





Function DBHealth(sSQLConnectionString, byRef oBag)



	Dim e

	Set e = New Error



	Dim cnADOConnection

	Set cnADOConnection = MomCreateObject("ADODB.Connection")

	cnADOConnection.Provider = "sqloledb"

	cnADOConnection.ConnectionTimeout = 15

	

	Dim sProv

	sProv = "Server=" &amp; sSQLConnectionString &amp; ";Database=master;Trusted_Connection=yes"

  

	e.Clear

	On Error Resume Next

		cnADOConnection.Open sProv

		e.Save

	On Error Goto 0    

	If 0 &lt;&gt; Err.number then

		'Error event to goin here

		DBHealth = SQL_DISCOVERY_CONNECT_FAILURE

		Exit Function

	End If

	    

    Dim oResults

	e.Clear

	On Error Resume Next

		Set oResults = cnADOConnection.Execute("SELECT name , ISNULL (DATABASEPROPERTYEX(name," &amp; "'" &amp; DB_PROPERTY &amp; "'),'N/A') from sys.databases")

		e.Save

	On Error Goto 0

	If e.Number &lt;&gt; 0 Then

		' Error event to go in here

		DBHealth = SQL_DISCOVERY_QUERY_FAILURE

		If (oResults &lt;&gt; null) Then oResults.Close

		Exit Function

    End If	

	



	Do While Not oResults.EOF

	

		Call oBag.AddValue(oResults(0) &amp; "-State", CStr(oResults(1)))					

		oResults.MoveNext

		

	Loop

	

	cnADOConnection.Close

  

End Function





Function MomCreateObject(ByVal sProgramId)

    Dim oError

    Set oError = New Error

    

	On Error Resume Next

	Set MomCreateObject = CreateObject(sProgramId)

	oError.Save

	On Error Goto 0

	

	If oError.Number &lt;&gt; 0 Then WScript.Quit

End Function



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 Default 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

</Script></ScriptBody>

          <TimeoutSeconds>$Config/TimeoutSeconds$</TimeoutSeconds>

        </DataSource>

      </MemberModules>

      <Composition>

        <Node ID="DS"/>

      </Composition>

    </Composite>

  </ModuleImplementation>

  <OutputType>System!System.PropertyBagData</OutputType>

</DataSourceModuleType>