Got a newbie SMO powershell question/issue.

  • Hello everyone,

    I'm quite new to PowerShell, but getting along OK with a PS cookbook, but I have run into an issue and I cant get around it, and looking for some help.

    I just wanted to list the service accounts, it all looks good but I am getting the following error when I run it which I am fairly sure relates to $

    PS SQLSERVER:\> $managedComputer.Services

    The following exception occurred while trying to enumerate the collection: "SQL Server WMI provider is not available on MyNamed\instance.".

    At line:1 char:1

    + $managedComputer.Services

    + ~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo : NotSpecified: (:) [], ExtendedTypeSystemException

    + FullyQualifiedErrorId : ExceptionInGetEnumerator

    I did import the module SQLPS, and their are no problems with that. My actual code is...

    #import SQL Server Module

    Import-Module SQLPS -DisableNameChecking

    $InstanceName = "MYNAMED\INSTANCE"

    $managedComputer = New-Object 'Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer' $InstanceName


    $managedComputer.Services |

    Select Name, ServiceAccount, DisplayName, ServiceState | Format-Table -AutoSize

    So I downloaded the SQL 2012 Feature Pack to get the SQLSysClrTypes, SharedManagementObjects, and PowerShellTools, which google led me to from the error I was receiving, and now face an additional issue whereby I cannot install the sharedmanagment objects because it says I need to install the SQLSysCLRTypes as a prerequisite, buts already installed. I also tried uninstalling, and also tried the 32 bit options.

    I'm enjoying learning PS, but this is just a pain. I am running it within the ISE in case its important.

    Any advice? PowerShell V3 BTW.

    Thank you.



  • This may obvious to most, but I worked this out in the end, I had to register the assemblies, for anyone else that may come across this, you install the SDK off the SQL Server media and then navigate to the SDK folder and find your assemblies (.dll) and register them in PowerShell

    Example: (you path may be different, I just registered everything I thought I might need)

    Add-Type -Path 'C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.sqlEnum.dll'

    Add-Type -Path 'C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.wmiEnum.dll'

    Add-Type -Path 'C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServerEnum.dll'

    Add-Type -Path 'C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.SmoExtended.dll'

    Add-Type -Path 'C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll'

    Add-Type -Path 'C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.SqlWmiManagement.dll'

    How to register SQL server assemblies in PowerShell



Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply