Exception calling "Invoke" error when loading inventory

Oct 23, 2013 at 9:12 PM
I'm working with Power Doc for the first time (awesome presentation at SQLPass Summit). I was able to create an inventory of the 2 instances on my Windows 8.1 laptop. I copied the .gz file to a new folder under my user\documents\powershell folder and ran

.\Convert-SqlServerInventoryClixmlToExcel.ps1 -FromPath "C:\Users\curtisk\Documents\WindowsPowerShell\Output\SQL Server Inventory - 2013-10-23-15-16.xml.gz"

The ISE (running in admin mode) immediately starts returning thousands of lines (which don't stop or show any progress in 10 minutes) reading

WARNING: Could not deserialize : Exception calling "Invoke" with "2" argument(s): "Parameter count mismatch."

Repeating the steps above with verbose logging writes the following to the log:

2013-10-23 16:10:05.3607 ? Starting Script: C:\Users\curtisk\Documents\WindowsPowerShell\Convert-SqlServerInventoryClixmlToExcel.ps1
2013-10-23 16:10:05.3607 ? Loading inventory from 'C:\Users\curtisk\Documents\WindowsPowerShell\Output\SQL Server Inventory - 2013-10-23-15-16.xml.gz'

Ideas? Help?
Oct 29, 2013 at 5:41 PM
I am getting a similar message...
this is from the Powershell console
in yellow
WARNING: Could not serialize psobject[]: Exception calling "Invoke" with "2" argument(s): "The Write is closed or in error state."
in red
Exception calling "Invoke" with "2" argument(s): "The writer is closed or in error state."
At C:\Users\mike\Documents\WindowsPowerShell\Modules\SqlServerInventory.psm1:121 char:3
  • [void]$done.invoke($serializer, @())
  • ~~~~~~~~~~~~~~~~~~~~~~~
    • CategoryInfo : NotSpecified: (:) [], MethodInvicationException
    • FullyQualifiedErrorId : InvalideOperationException
then for the log I had verbose turned on and the last lines are
2013-10-29 11:19:37.3280 ? Scanned BHM-DOCS-SQL at IP address with 0 errors
2013-10-29 11:19:37.6712 ? Machine scan complete (Success: 6; Failure: 0)
2013-10-29 11:19:37.7180 ? Writing Inventory to disk

I have run it a couple of times without issue... so not sure whats up...
but wanted to let you know
Thanks for writing this ... it is awesome...
Oct 29, 2013 at 7:11 PM
more information I was running with command .\Get-SqlServerInventoryToClixml.ps1 -Computername My-Server -LoggingPreference Verbose -DirectoryPath C:\Users\mike\Documents\SQLDocs -IncludeDatabaseObjectInformation -IncludeDatabaseObjectPermissions -IncludeDatabaseSystemObjects

when I removed the -IncludeDatabaseObjectInformation -IncludeDatabaseObjectPermissions options it worked fine... it was running against a SQL 2000 server
Oct 30, 2013 at 10:57 PM
I've figured out what's going on here - an issue was introduced with PowerShell 4.0 and how the .NET methods for serialization/deserialization are being invoked via reflection. I've uploaded BETA 1 with the fix. You can grab it from https://sqlpowerdoc.codeplex.com/releases/view/114187
Nov 14, 2013 at 8:27 AM
Thank you for the beta fix.
I've re-run the data collection part Get-SqlServerInventoryToClixml.ps1.

Apparently the serialization issue has been fixed for the Convert-SqlServerInventoryClixmlToExcel.ps1.
On my Win7 x86 sp1 with PoSh4 and office 2007 it now produces :

Exception calling "Add" with "0" argument(s): "Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))"
At C:\Users\ItsMe\Documents\WindowsPowerShell\Modules\SqlServerInventory\SqlServerInventory.psm1:4657 char:3
  • $Workbook = $Excel.Workbooks.Add()
  • ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    • CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    • FullyQualifiedErrorId : ComMethodTargetInvocation
I'll request an upgrade to Office 2010 and see if that still produces this issue.

Nov 14, 2013 at 4:42 PM
I tested the latest version this morning on a VM running Windows 7 N SP 1 with .NET Framework 4.5.1, PoSh 4.0, and Office 2007 (x86) RTM. I was able to create all the Excel workbooks with no problem.

I think I might have found a similar issue to what you're experiencing - see http://stackoverflow.com/questions/687891/exception-automating-excel-2007-with-powershell-when-calling-workbooks-add

In a PoSh console, for $Home is your CurrentCulture set to something other than en-US?

Nov 14, 2013 at 7:50 PM
ah, indeed. Current culture is nl-be.
I'll test it tomorrow.

Thank you for the follow up.

Nov 14, 2013 at 7:53 PM
OK, the temporary fix is to change your culture to EN-US (via Regions and Language in control panel) to create the Excel workbooks. Not a great fix, but it'll work while I look into how to fix it in the code.

(I've reproduced the problem by changing the culture in my VM as well, so now I can test that a fix works too!)

Nov 18, 2013 at 1:44 PM
as a workaround, not messing around with my client settings, and getting confused with azerty-querty keyboard switch, this can be a great option:
As you can see I grabbed this function from a technet forum.
#Region XLSStuff
# http://social.technet.microsoft.com/Forums/en-US/908dea46-1bb9-492f-b85a-3d5972765c03/adding-new-sheets-to-excel-workbook 
Function Use-Culture ([System.Globalization.CultureInfo]$Culture,[ScriptBlock]$Script)
    # What does this code do? Basically, it sort of resolves and age-old problem with Excel
    # (or probably with the whole Office suite, but I never tried stuff in Word or Access with
    # PowerShell) and internationalisation.
    # For certain code to run properly, the internal culture should be set to EN-US. EN-UK might
    # work too, but expect a lot of HRESULT errors when trying to execute automatisation code
    # on non-English systems. This code runs the current thread as EN-US, whatever the culture
    # of the Operating System. At the end of the script, it restores the old culture settings.
    # Because the whole Excel (or Office) related code runs inside the script block given to this
    # function, there's one huge disadvantage however for debugging: any error will probably refer
    # to this Use-Culture code block. 
    $OldCulture = [System.Threading.Thread]::CurrentThread.CurrentCulture
        [System.Threading.Thread]::CurrentThread.CurrentCulture = $OldCulture
    [System.Threading.Thread]::CurrentThread.CurrentCulture = $Culture
    Invoke-Command $Script
    [System.Threading.Thread]::CurrentThread.CurrentCulture = $OldCulture
#EndRegion XLSStuff

#How do you run it?
Use-Culture EN-US {
        & .\Convert-SqlServerInventoryClixmlToExcel.ps1 -FromPath "$TargetFile" -ToDirectoryPath "$TargetPath" 

Feb 19, 2014 at 7:28 AM
Edited Feb 19, 2014 at 7:29 AM
Hi Kendal i know this thread is maybe a little old but I found an easier solution to the "could not deserialize issue" I get this error on my Windows 8 machine and this is what I did to resolve the issue:
open a command prompt and then just start Windows Powershell in mode 2.0. like this :
Powershell.exe -version 2
then use the .\Convert-SqlServerInventoryClixmlToExcel.ps1 as per manual and it just works fine.
thought I would just share this bit of information to anyone that came across this problem. On my Windows 7 machine with a different locale/culture than en-us the script worked, it was just on my windows 8 machine that it did not work.
p.s. also make sure under windows features that Windows Powershell 2.0. is also selected and installed.
Hope this helps someone Cheers
Eugene van den Bergh
Mar 5, 2014 at 9:25 PM
Thanks for the tip. So I understand, your Windows 8 machine where the code does not work is a different locale/culture than en-us?

Mar 7, 2014 at 5:33 AM
Hi Kendal yes I am using South African English

Sent from Windows Mail

Oct 27, 2014 at 2:10 PM
Edited Oct 27, 2014 at 2:12 PM
sorry, what i wrote, didt work after all..