Starting with SQL Server 2005 WMI with help of Scriptomatic

  • Article

SQL Server 2005 introduces a new WMI Provider that allows configuration of Services, Client Network Protocols, and Server Protocols. This is a great improvement for DBA that are managing many SQL Servers, as WMI allows you to query and modify SQL Server configuration settings remotely.

Now, everyone who has worked with WMI knows it's not easy to write a WMI program. You need to know the namespace, class names, etc. Everything that helps developers so well in VS (IntelliSense, object browsers) is not available to the scripters that are working on a bare system. I typically have a couple of WMI templates on my pen drive and start from there.

I recently ran into Scriptomatic. It's a great tool to give you good starting points for creating WMI scripts. It provides you with a dropdown of namespaces available on the system, and classes within the selected namespace. It even allows creation of Perl scripts...!

Download it here and then select the root\Microsoft\SqlServer\ComputerManagement namespace; select the SqlService class and voila, a ready to run script is displayed in the window. Quite nice! Here is an VbScript example:

On Error Resume Next

Const wbemFlagReturnImmediately = &h10
Const wbemFlagForwardOnly = &h20

arrComputers = Array("C3PO")
For Each strComputer In arrComputers
WScript.Echo
WScript.Echo "=========================================="
WScript.Echo "Computer: " & strComputer
WScript.Echo "=========================================="

   Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\Microsoft\SqlServer\ComputerManagement")
Set colItems = objWMIService.ExecQuery("SELECT * FROM SqlService", "WQL", _
wbemFlagReturnImmediately + wbemFlagForwardOnly)

   For Each objItem In colItems
WScript.Echo "AcceptPause: " & objItem.AcceptPause
WScript.Echo "AcceptStop: " & objItem.AcceptStop
strAdvancedProperties = Join(objItem.AdvancedProperties, ",")
WScript.Echo "AdvancedProperties: " & strAdvancedProperties
WScript.Echo "BinaryPath: " & objItem.BinaryPath
WScript.Echo "Clustered: " & objItem.Clustered
strDependencies = Join(objItem.Dependencies, ",")
WScript.Echo "Dependencies: " & strDependencies
WScript.Echo "Description: " & objItem.Description
WScript.Echo "DisplayName: " & objItem.DisplayName
WScript.Echo "ErrorControl: " & objItem.ErrorControl
WScript.Echo "ExitCode: " & objItem.ExitCode
WScript.Echo "HostName: " & objItem.HostName
WScript.Echo "ProcessId: " & objItem.ProcessId
WScript.Echo "ServiceName: " & objItem.ServiceName
WScript.Echo "SQLServiceType: " & objItem.SQLServiceType
WScript.Echo "StartMode: " & objItem.StartMode
WScript.Echo "StartName: " & objItem.StartName
WScript.Echo "State: " & objItem.State
WScript.Echo
Next
Next