Get-SqlServerInventoryToClixml.ps1 failing

May 14, 2013 at 4:08 PM
Edited May 14, 2013 at 4:08 PM
command was "\Get-SqlServerInventoryToClixml.ps1 -computername abc.xyz.lan -IncludeDatabaseObjectInformation -LoggingPreference verbose"

the logfile shows the script connects to the server and then fails with:

2013-05-14 17:03:51.6746 ? ERROR: Unable to retrieve service information from abc.xyz.LAN (***): Exception calling "EndInvoke" with "1" argument(s): "You cannot call a method on a null-valued expression."

what's that supposed to mean?
Coordinator
May 15, 2013 at 3:15 AM
Thanks for checking out SQL Power Doc! This error could be happening if SMO is not installed on the machine where you're running the scripts.

Have you verified that SMO is installed?

Kendal
May 15, 2013 at 10:54 AM
argh. thanks, i actually did read the requirements. i just didn't check if the sql management studio was installed on the server i was trying it from because i was sure it was...

jozi
Feb 25, 2014 at 10:15 PM
So, I've been using this for the last few months now, and I've been generating the reports monthly. This month I have 3 servers that are getting the below error, does this mean SMO was un-installed? I ran this at least 2 times on these servers.

Unable to retrieve service information from SERVERNAME/IPADDRESS: Exception calling "EndInvoke" with "1" argument(s): "The following exception occurred while trying to enumerate the collection: "An exception occurred in SMO while trying to manage a service."."

I LOVE this script btw!

Thanks,
Cindy
Coordinator
Mar 3, 2014 at 9:53 PM
Hi Cindy,
I'm glad you like the script! :-)

It looks like something's up with SMO on the machine where you're running SQL Power Doc from. Can you try running this snippet of code on the same machine (Change $ServerName on line 10 to the name of one of those 3 problem servers) and paste the full contents of $Error in this thread (sanitize any computer names, of course!)?
$Error.clear()
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | ForEach-Object {
    if ($_.GetName().Version.Major -ge 10) {
        [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | Out-Null
        [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement') | Out-Null
    }
}

<# Change this to the name of the server that's giving you problems #>
$ServerName = 'ServerName'

$ManagedComputer = New-Object -TypeName 'Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer' -ArgumentList $ServerName
$ManagedComputer.Services | Out-Null
$Error
This is a very basic version of the same code that SQL Power Doc is running but should give a more specific error message that I can use to help troubleshoot.

--Kendal
Mar 3, 2014 at 11:17 PM
Hmmmmm..... So, now I'm even more confused. I run the SQL Power Doc from my desktop, under my creds, as my creds have access to all the sql servers. If SMO wasn't working on my desktop, I would expect none of the servers to give me the output file, but it's only 3 out of like 12 that I'm having the issue with. If I run the command posted above from my desktop, and put in a 'ServerName' on line 10, that I'm having an issue w/ , I get the following... is this what you expected?

The following exception occurred while trying to enumerate the collection: "An exception occurred in SMO while trying
to manage a service.".
At line:1 char:1
  • $ManagedComputer.Services | Out-Null
  • ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    • CategoryInfo : NotSpecified: (:) [], ExtendedTypeSystemException
    • FullyQualifiedErrorId : ExceptionInGetEnumerator
Or did I not run it correctly w/in powershell? I'm thinking I didnt' run it correctly in powershell....

Thanks,
Cindy
Coordinator
Mar 5, 2014 at 9:20 PM
Cindy,
It looks like you ran the command properly. The error suggests you've either got an issue with WMI on the server that you're trying to get information from or a problem with SMO on your local machine. Is the version of SMO on your machine the same as or higher than the version of SQL Server on the machine that you're getting errors back from?

--Kendal