BgInfo, WMI and SQL Server
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
Print article | This entry was posted by tosc on 2008-06-27 at 11:23:09 . Follow any responses to this post through RSS 2.0. |
Tag cloud
backup «best practices» books bug configuration ctp «cumulative update» demo denali dmv ebook humor index indexoptimize integrity kbfix loginproperty maintenance «ms sql server 2008» pass performance php profiler reviews «ross mistry» «service pack» «service pack 2» «service packs» sharepoint «sql 2012» «sql server» «sql server 2005» «sql server 2008 r2» «sql server 2012» «sql server 2014» sqlcat sqlpass «sqlpass franken» ssms ssmstoolspack «stacia misner» sysadmin t-sql technet «technical note» tempdb tools «trace flag» upgrade whitepapers