I'm trying to use the SMO WMI API/objects in PowerShell 2.0 on Windows 7 with SQL Server 2008 R2 installed to get a list of SQL Server instances on the local computer using the Managed Comuter object. However, I'm getting exceptions after I instantiate the objects when I try to access any data on them. I'm running PowerShell as an administrator.
$computer = New-Object Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer ($env:computername)
$computer.ServerInstances
Results in this error:
The following exception was thrown when trying to enumerate the collection: "An exception occurred in SMO while trying to manage a service.".
At line:1 char:89
+ (New-Object Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer ($env:computername)). <<<< ServerInstances
+ CategoryInfo : NotSpecified: (:) [], ExtendedTypeSystemException
+ FullyQualifiedErrorId : ExceptionInGetEnumerator
Is there some service I have to enable to get this to work? The WMI service is running. Is there some other setting I need? Why can't I enumerate SQL Server instances?
Best Answer
Are you running this code as administrator? Access to WMI requires WMI privileges. However, there is an alternate method I use. I documented that here: http://www.powershellmagazine.com/2013/04/24/pstip-enumerate-all-sql-server-instances-in-a-network/