BgInfo from Sysinternals (now Windows Sysinternals, by Mark Russinovich) automatically displays relevant information about a Windows computer on the desktop's background, such as the computer name, IP address, service pack version, and more interesting features like WMI query results!

Often when I walked up to several systems in our data center by using a console switch, I have quickly to know important aspects of theirs configurations and if all SQL-Servers Services are running.

And there for I make a small script, which I use with

...

BgInfo, to display the SQL-Services and other informations.

The VB-script use a WMI query and check up SQL-Server properties. It looks like this:

On Error Resume Next
Const wbemFlagReturnImmediately = &h10
Const wbemFlagForwardOnly = &h20
i = 0

strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & _
"\root\CIMV2")
Set colItems = objWMIService.ExecQuery("SELECT * FROM " _
&"Win32_Service WHERE Name = 'MSSQLSERVER' " _
&"OR Name = 'SQLBrowser' OR Name = 'SQLSERVERAGENT' " _
&"OR Name = 'SQLWriter' OR Name = 'MsDtsServer' " _
&"OR Name = 'msftesql' OR Name = 'MSSQLServerOLAPService'" _
&"", "WQL", wbemFlagReturnImmediately + wbemFlagForwardOnly)

strConn = "Driver={SQL Server};SERVER=" & strComputer 
    On Error Resume Next
Set objDB = CreateObject("ADODB.Connection")

For Each objItem in colItems
  If objItem.state <> "Running" Then
     Echo "******************************************"
     Echo objItem.Name & " is "  & objItem.state 
     Echo "******************************************"
  Else
     Echo objItem.Name & " is " & objItem.state
     If objItem.Name = "MSSQLSERVER" Then i = 1
  End If
Next
    
If i = 1 Then 
  objDB.Open(strConn)
   If err.number <> 0 then 
      quitit 
   Else 
      sqlVers
   End If
ElseIf i=0 then quitit
End If
 
Sub sqlVers() 
 strSQL = "select v=@@version" 
 Set objRS = objDB.Execute(strSQL) 
 If err.number <> 0 Then 
    quitit 
   Exit Sub 
 End if 
 strVer = objRS.Fields("v").value 
 If err.number <> 0 Then 
    quitit 
   Exit sub 
 End If 
 strSQL = " + CAST(SERVERPROPERTY ('productlevel')" _
   & " AS varchar(30)) + ', Build ' " _
   & " + CAST( SERVERPROPERTY('ProductVersion')" _
   & " AS varchar(30)) + ', ' " _
   & " + CAST( SERVERPROPERTY('Edition')" _
   & " AS varchar(30)) + ', ' " _
   & " + (CASE SERVERPROPERTY('EngineEdition')" _
   & " WHEN 1 THEN 'Personal or Desktop'" _
   & " WHEN 2 THEN 'Standard'" _
   & " WHEN 3 THEN 'Enterprise'" _
   & " WHEN 4 THEN 'Express'" _
   & " END + ' Database Engine Edition')"
   
 If instr(strVer, "2005 - 9") > 0 Then 
  strSQL = "SELECT v= 'SQL Server 2005 '" + strSQL 
  Set objRS = objDB.Execute(strSQL) 
  strVer = objRS.Fields("v").value  
 ElseIf instr(strVer, "2000 - 8") > 0 Then 
  strSQL = "SELECT v= 'SQL Server 2000 '" + strSQL
  Set objRS = objDB.Execute(strSQL) 
  strVer = objRS.Fields("v").value 
 ElseIf instr(strVer, "7.00.1063") Then 
    strVer = "SQL Server 7.0 SP4" 
 ElseIf instr(strVer, "7.00.961") Then 
    strVer = "SQL Server 7.0 SP3" 
 ElseIf instr(strVer, "7.00.842") Then 
    strVer = "SQL Server 7.0 SP2" 
 ElseIf instr(strVer, "7.00.699") Then 
    strVer = "SQL Server 7.0 SP1" 
 ElseIf instr(strVer, "7.00.623") Then 
    strVer = "SQL Server 7.0" 
 End If 
 Echo strVer 
End Sub 
Sub quitit() 
    Echo "(Sorry, No Bonus!)" 
End Sub 

And the result :-)

CU
tosc