WEB LISTING 1: VB Solution That Shows Which UMS Controls a SPID

Option Explicit

Dim oConnection, ConnectionString, oRecordset, Message

Set oConnection = CreateObject("ADODB.Connection")

ConnectionString = _
   "Provider=SQLOLEDB;" & _
   "Data Source=ServerName;" & _
   "Integrated Security=SSPI;"

oConnection.Open ConnectionString
oConnection.CursorLocation = 3

' Get SPID list and find SchedulerID for each.
Set oRecordSet = oConnection.Execute("SELECT DISTINCT spid
    FROM master..sysprocesses")
oConnection.CursorLocation = 1
Do While oRecordSet.EOF = False
   Message = Message & GetSchedulerId(oConnection, oRecordSet.Fields(0).Value)
   oRecordSet.MoveNext
Loop

oRecordSet.Close
Set oRecordSet = Nothing
oConnection.Close
Set oConnection = Nothing

WScript.Echo Message                               ' Show SPID/ServiceId list.

Function GetSchedulerId(oConnection, SPID)
   Dim oError

' Get SchedulerID.
   oConnection.Execute "DBCC TRACEON(3604)"                ' Send results to client.
   oConnection.Execute "DBCC PSS(0, " & CStr(SPID) & ")" ' Get info for this SPID.
   ' Find in DBCC PSS output SchedulerId that's returned in connection errors collection.
   For Each oError In oConnection.Errors
      If InStr(oError.Description, "(SchedulerId) = 0x") > 0 Then
         ' Return SPID and SchedulerId.
         GetSchedulerId = "SPID " & CStr(SPID) & _
            " SchedulerID is " & Right(Trim(oError.Description), 6)
         Exit Function
      End If
   Next
   ' SchedulerId string not found -- return SPID and "unknown."
   GetSchedulerId = "SPID " & CStr(SPID) & _
      " SchedulerID is unknown "
End Function