SQL 2000 Blocking SPIDs Provider

Microsoft.SQLServer.2000.BlockingSPIDsProvider (DataSourceModuleType)

Element properties:

Member Modules:

Overrideable Parameters:

Source Code:

<DataSourceModuleType ID="Microsoft.SQLServer.2000.BlockingSPIDsProvider" Accessibility="Internal" RunAs="SQL!Microsoft.SQLServer.SQLProbeAccount">

  <Configuration>

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

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

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

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

    <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="WaitMinutes" ParameterType="int" Selector="$Config/IntervalSeconds$"/>

    <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>GetSQL2000BlockingSPIDs.vbs</ScriptName>

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

          <ScriptBody><Script>

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

Option Explicit

SetLocale("en-us")





'Start of Main

'-----------------------------------------------------------------------------------------------

call Main()



Sub Main()



	Dim objParameters, sConnectionString

	Dim oAPI, oBag

	Dim iTime



	Set objParameters = WScript.Arguments



	If objParameters.Count &lt;&gt; 2 Then

		Quit()

	End If



	sConnectionString= objParameters(0)

	iTime = objParameters(1)

	

	Set objParameters = Nothing



	Set oAPI = MOMCreateObject("MOM.ScriptAPI")

	Set oBag = oAPI.CreatePropertyBag()



	If CheckBlockedSPIDS(oBag, sConnectionString, iTime) = 0 Then

		Call oAPI.Return(oBag)

	Else

		Quit()

	End If



End Sub

'End of Main

'-----------------------------------------------------------------------------------------------





Function Quit()

	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





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 ThrowScriptError "Unable to create automation object '" &amp; sProgramId &amp; "'", oError

End Function







Function ThrowScriptError(Byval sMessage, ByVal oErr)  

  On Error Resume Next

  Dim oAPITemp	

	Set oAPITemp = MOMCreateObject("MOM.ScriptAPI")

  oAPITemp.LogScriptEvent "GetSQL2000BlockingSPIDs.vbs", 4000, 1, sMessage &amp; ". " &amp; oErr.Description

  Quit()

End Function



Function CheckBlockedSPIDS(ByRef oBag, ByVal sConnectionString, ByVal iWaitInMinutes)

	dim cnADOConnection

	dim rsBlockedSPIDS

	dim strProv

	dim sName

	dim bFail

	dim SCRIPT_SQL 

	dim SPIDList

	dim SPIDCount



	SCRIPT_SQL = "SELECT  " &amp; _

		            "dbo.sysprocesses.spid, " &amp; _

		            "dbo.sysprocesses.blocked, " &amp; _

		            "dbo.sysprocesses.waittime, " &amp; _

		            "dbo.sysprocesses.waitresource, " &amp; _

		            "dbo.sysprocesses.program_name, " &amp; _

		            "dbo.sysprocesses.loginame, " &amp; _

		            "dbo.sysdatabases.name " &amp; _

		            "FROM dbo.sysprocesses WITH (nolock) " &amp; _

		            "INNER JOIN dbo.sysdatabases " &amp; _

		            "ON dbo.sysprocesses.dbid = dbo.sysdatabases.dbid " &amp; _

		            "WHERE     (dbo.sysprocesses.blocked &lt;&gt; 0) " &amp; _

		            "AND (dbo.sysprocesses.waittime &gt; " &amp; CStr(iWaitInMinutes*60) &amp; " * 1000) " &amp; _

		            "ORDER BY dbo.sysprocesses.waittime DESC"



	 

	On Error resume next



		Set cnADOConnection = MomCreateObject("ADODB.Connection")

		cnADOConnection.Provider = "sqloledb"

		cnADOConnection.ConnectionTimeout = 30

		strProv = "Server=" &amp; sConnectionString &amp; ";Database=master;Trusted_Connection=yes"



        Err.Clear

		cnADOConnection.Open strProv

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

			Exit Function

		end if





        Err.Clear

		Set rsBlockedSPIDS = cnADOConnection.Execute(SCRIPT_SQL)

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

			Exit Function

		end if



		SPIDList = ""

		SPIDCount = 0

		do	

			bFail = True

			Err.Clear

			if rsBlockedSPIDS.EOF then

				if 0 &lt;&gt; Err.number then Exit Do

				bFail = False

				Exit Do

			end if

			if 0 &lt;&gt; Err.number then Exit Do



			SPIDCount = SPIDCount + 1



			if SPIDList = "" Then

				SPIDList = CStr(rsBlockedSPIDS("Spid").Value)

			else

				SPIDList = SPIDList &amp; "," &amp; CStr(rsBlockedSPIDS("Spid").Value)

			end if



'			sAlertDescription = "The program """ &amp; Trim(rsBlockedSPIDS("Program_name").Value) &amp; _

'			                """ has been blocked for " &amp; CStr((CLng(rsBlockedSPIDS("waittime").Value) / 1000) / 60) &amp; _

'			                " minutes on database " &amp; rsBlockedSPIDS("name").Value &amp; _

'			                " in the SQL instance " &amp; sInstance &amp; _

'			                ".  The defined acceptable blocking threshold is " &amp; CStr(iWaitInMinutes) &amp; _

'			                " minute(s).  """ &amp; Trim(rsBlockedSPIDS("Program_name").Value) &amp; _

'			                """ is running on SPID " &amp; CStr(rsBlockedSPIDS("Spid").Value) &amp; _

'			                " as login " &amp; Trim(rsBlockedSPIDS("Loginame").Value) &amp; _

'			                " and is blocked by SPID " &amp; CStr(rsBlockedSPIDS("Blocked").Value) &amp; _

'			                ".  The resource id is " &amp; rsBlockedSPIDS("Waitresource").Value &amp; "."

			

			Err.Clear

			if 0 &lt;&gt; Err.number then Exit Do

			

            Err.Clear

			rsBlockedSPIDS.MoveNext

			if 0 &lt;&gt; Err.number then Exit Do

		Loop

		Call oBag.AddValue("BlockedSPIDList",CStr(SPIDList))

		Call oBag.AddValue("BlockedSPIDCount",CInt(SPIDCount))

	

	if bFail then

		Exit Function

	end if



	Set cnADOConnection = Nothing

	Set rsBlockedSPIDS = Nothing



	CheckBlockedSPIDS = 0

End Function





										</Script></ScriptBody>

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

        </DataSource>

      </MemberModules>

      <Composition>

        <Node ID="DS"/>

      </Composition>

    </Composite>

  </ModuleImplementation>

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

</DataSourceModuleType>