Extract runs but doesn't write data

Apr 24, 2013 at 8:54 PM
The PowerShell executes but won't write out any data.

The inventory file is produced but is only about 13KB in size. I am running the extract for 1 server with 2 SQL Server instances. The runtime is about 15 minutes.

Here is a small sample of what I am seeing in the inventory file:

2013-04-24 12:50:27.7654 ? Beginning scan of 2 instance(s)
2013-04-24 12:50:27.9442 ? Gathering information from ELYS7102.CORP.KNORR-BREMSE.COM\BPC_DEV at 149.244.64.102 [Instance 1 of 2]
2013-04-24 12:50:27.9539 ? Gathering information from ELYS7102.CORP.KNORR-BREMSE.COM\BPC_PRD at 149.244.64.102 [Instance 2 of 2]
2013-04-24 12:50:28.8436 $ [ELYS7102.CORP.KNORR-BREMSE.COM\BPC_PRD] Gathering Server service information
2013-04-24 12:50:28.8436 $ [ELYS7102.CORP.KNORR-BREMSE.COM\BPC_DEV] Gathering Server service information
2013-04-24 12:50:29.7235 $ [ELYS7102.CORP.KNORR-BREMSE.COM\BPC_PRD] Gathering Server configuration information
2013-04-24 12:50:30.9667 $ [ELYS7102.CORP.KNORR-BREMSE.COM\BPC_PRD] Gathering Database information
2013-04-24 12:50:31.2675 $ [ELYS7102.CORP.KNORR-BREMSE.COM\BPC_DEV] Gathering Server configuration information
2013-04-24 12:50:32.3291 $ [ELYS7102.CORP.KNORR-BREMSE.COM\BPC_DEV] Gathering Database information
2013-04-24 13:05:01.2853 $ [ELYS7102.CORP.KNORR-BREMSE.COM\BPC_DEV] Gathering Server security information
2013-04-24 13:05:01.5705 $ [ELYS7102.CORP.KNORR-BREMSE.COM\BPC_DEV] Gathering Endpoint information
2013-04-24 13:05:01.6467 $ [ELYS7102.CORP.KNORR-BREMSE.COM\BPC_DEV] Gathering Server linked server information
2013-04-24 13:05:01.6818 $ [ELYS7102.CORP.KNORR-BREMSE.COM\BPC_DEV] Gathering Server startup procedure information
2013-04-24 13:05:01.7551 $ [ELYS7102.CORP.KNORR-BREMSE.COM\BPC_DEV] Gathering Server Trigger information
2013-04-24 13:05:01.8303 $ [ELYS7102.CORP.KNORR-BREMSE.COM\BPC_DEV] Gathering Resource Governor information
2013-04-24 13:05:01.9465 $ [ELYS7102.CORP.KNORR-BREMSE.COM\BPC_DEV] Gathering SQL Trace information
2013-04-24 13:05:02.2521 $ [ELYS7102.CORP.KNORR-BREMSE.COM\BPC_DEV] Gathering Trace Flag information
2013-04-24 13:05:02.3674 $ [ELYS7102.CORP.KNORR-BREMSE.COM\BPC_DEV] Gathering Database Mail information
2013-04-24 13:05:02.6418 $ [ELYS7102.CORP.KNORR-BREMSE.COM\BPC_DEV] Gathering SQL Agent service information
2013-04-24 13:05:02.7199 $ [ELYS7102.CORP.KNORR-BREMSE.COM\BPC_DEV] Gathering SQL Agent configuration information
2013-04-24 13:05:02.7951 $ [ELYS7102.CORP.KNORR-BREMSE.COM\BPC_DEV] Gathering SQL Agent job information
2013-04-24 13:05:03.6584 $ [ELYS7102.CORP.KNORR-BREMSE.COM\BPC_DEV] Gathering SQL Agent alert information
2013-04-24 13:05:03.6692 $ [ELYS7102.CORP.KNORR-BREMSE.COM\BPC_DEV] Gathering SQL Agent operator information
2013-04-24 13:05:03.9602 ? Scanned ELYS7102.CORP.KNORR-BREMSE.COM\BPC_DEV with 0 errors


Any ieda on what is going on?


Dave Schaeffer
Coordinator
Apr 24, 2013 at 9:00 PM
Dave,
Did you then run Convert-SqlServerInventoryClixmlToExcel.ps1 against the 13KB file to write the Excel workbooks? If not, that's the next step. If so, and no Excel workbooks were created, let me know and we'll pick up troubleshooting from there.

Kendal
Apr 24, 2013 at 9:40 PM

Hi Kendal,

Yes, I have tried to run the Convert…… Here is the command:

.\Convert-SqlServerInventoryClixmlToExcel.ps1 -FromPath "C:\Users\e001633\Documents\SQL Server Inventory - 2013-04-24-12-49.xml" -ColorTheme Blue

Here is the results:

New-Object : Exception calling ".ctor" with "2" argument(s): "Could not find file 'C:\Users\e001633\Documents\SQL Serv

r Inventory - 2013-04-24-12-49.xml'."

At C:\Users\e001633\Documents\WindowsPowerShell\Modules\SqlServerInventory\SqlServerInventory.psm1:230 char:28

+ $FileStream = New-Object <<<< -Typename System.IO.FileStream($InputObject, [System.IO.FileMode]::Open)

+ CategoryInfo : InvalidOperation: (:) [New-Object], MethodInvocationException

+ FullyQualifiedErrorId : ConstructorInvokedThrowException,Microsoft.PowerShell.Commands.NewObjectCommand

New-Object : Exception calling ".ctor" with "3" argument(s): "Value cannot be null.

Parameter name: stream"

At C:\Users\e001633\Documents\WindowsPowerShell\Modules\SqlServerInventory\SqlServerInventory.psm1:231 char:28

+ $GZipStream = New-Object <<<< -TypeName System.IO.Compression.GZipStream($FileStream, [System.IO.Compre

sion.CompressionMode]::Decompress, $false)

+ CategoryInfo : InvalidOperation: (:) [New-Object], MethodInvocationException

+ FullyQualifiedErrorId : ConstructorInvokedThrowException,Microsoft.PowerShell.Commands.NewObjectCommand

Multiple ambiguous overloads found for "Create" and the argument count: "1".

At C:\Users\e001633\Documents\WindowsPowerShell\Modules\SqlServerInventory\SqlServerInventory.psm1:232 char:44

+ $xr = [System.Xml.XmlTextReader]::Create <<<< ($GZipStream)

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

+ FullyQualifiedErrorId : MethodCountCouldNotFindBest

Exception calling "Invoke" with "1" argument(s): "Parameter count mismatch."

At C:\Users\e001633\Documents\WindowsPowerShell\Modules\SqlServerInventory\SqlServerInventory.psm1:233 char:32

+ $deserializer = $ctor.Invoke <<<< ($xr)

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

+ FullyQualifiedErrorId : DotNetMethodException

Exception calling "Invoke" with "2" argument(s): "Non-static method requires a target."

At C:\Users\e001633\Documents\WindowsPowerShell\Modules\SqlServerInventory\SqlServerInventory.psm1:236 char:24

+ while (!$done.Invoke <<<< ($deserializer, @()))

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

+ FullyQualifiedErrorId : DotNetMethodException

You cannot call a method on a null-valued expression.

At C:\Users\e001633\Documents\WindowsPowerShell\Modules\SqlServerInventory\SqlServerInventory.psm1:244 char:13

+ $xr.Close <<<< ()

+ CategoryInfo : InvalidOperation: (Close:String) [], RuntimeException

+ FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.

At C:\Users\e001633\Documents\WindowsPowerShell\Modules\SqlServerInventory\SqlServerInventory.psm1:245 char:23

+ $GZipStream.Dispose <<<< ()

+ CategoryInfo : InvalidOperation: (Dispose:String) [], RuntimeException

+ FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.

At C:\Users\e001633\Documents\WindowsPowerShell\Modules\SqlServerInventory\SqlServerInventory.psm1:246 char:23

+ $FileStream.Dispose <<<< ()

+ CategoryInfo : InvalidOperation: (Dispose:String) [], RuntimeException

+ FullyQualifiedErrorId : InvokeMethodOnNull

Remove-Variable : Cannot find a variable with name 'FileStream'.

At C:\Users\e001633\Documents\WindowsPowerShell\Modules\SqlServerInventory\SqlServerInventory.psm1:252 char:18

+ Remove-Variable <<<< -Name type, ctor, FileStream, GZipStream, xr, deserializer, method, done

+ CategoryInfo : ObjectNotFound: (FileStream:String) [Remove-Variable], ItemNotFoundException

+ FullyQualifiedErrorId : VariableNotFound,Microsoft.PowerShell.Commands.RemoveVariableCommand

Remove-Variable : Cannot find a variable with name 'GZipStream'.

At C:\Users\e001633\Documents\WindowsPowerShell\Modules\SqlServerInventory\SqlServerInventory.psm1:252 char:18

+ Remove-Variable <<<< -Name type, ctor, FileStream, GZipStream, xr, deserializer, method, done

+ CategoryInfo : ObjectNotFound: (GZipStream:String) [Remove-Variable], ItemNotFoundException

+ FullyQualifiedErrorId : VariableNotFound,Microsoft.PowerShell.Commands.RemoveVariableCommand

Remove-Variable : Cannot find a variable with name 'xr'.

At C:\Users\e001633\Documents\WindowsPowerShell\Modules\SqlServerInventory\SqlServerInventory.psm1:252 char:18

+ Remove-Variable <<<< -Name type, ctor, FileStream, GZipStream, xr, deserializer, method, done

+ CategoryInfo : ObjectNotFound: (xr:String) [Remove-Variable], ItemNotFoundException

+ FullyQualifiedErrorId : VariableNotFound,Microsoft.PowerShell.Commands.RemoveVariableCommand

Remove-Variable : Cannot find a variable with name 'deserializer'.

At C:\Users\e001633\Documents\WindowsPowerShell\Modules\SqlServerInventory\SqlServerInventory.psm1:252 char:18

+ Remove-Variable <<<< -Name type, ctor, FileStream, GZipStream, xr, deserializer, method, done

+ CategoryInfo : ObjectNotFound: (deserializer:String) [Remove-Variable], ItemNotFoundException

+ FullyQualifiedErrorId : VariableNotFound,Microsoft.PowerShell.Commands.RemoveVariableCommand

ConvertFrom-GzCliXml : Cannot validate argument on parameter 'InputObject'. The argument is null or empty. Supply an a

gument that is not null or empty and then try the command again.

At C:\Users\e001633\Documents\WindowsPowerShell\Modules\SqlServerInventory\SqlServerInventory.psm1:630 char:38

+ ConvertFrom-GzCliXml -InputObject <<<< $([System.Convert]::FromBase64String($_))

+ CategoryInfo : InvalidData: (:) [ConvertFrom-GzCliXml], ParameterBindingValidationException

+ FullyQualifiedErrorId : ParameterArgumentValidationError,ConvertFrom-GzCliXml

Property 'DatabaseServer' cannot be found on this object; make sure it exists and is settable.

At C:\Users\e001633\Documents\WindowsPowerShell\Modules\SqlServerInventory\SqlServerInventory.psm1:629 char:15

+ $Inventory. <<<< DatabaseServer = $Inventory.DatabaseServer | ForEach-Object {

+ CategoryInfo : InvalidOperation: (DatabaseServer:String) [], RuntimeException

+ FullyQualifiedErrorId : PropertyNotFound

Export-SqlServerInventoryToExcel : Cannot bind argument to parameter 'SqlServerInventory' because it is null.

At C:\Users\e001633\Documents\WindowsPowerShell\Convert-SqlServerInventoryClixmlToExcel.ps1:181 char:20

+ -SqlServerInventory <<<< $Inventory `

+ CategoryInfo : InvalidData: (:) [Export-SqlServerInventoryToExcel], ParameterBindingValidationException

+ FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Export-SqlServerInventoryToExcel

Dave

Coordinator
Apr 24, 2013 at 9:49 PM
Ah, it looks like the .gz was left off the filename. Try this:
.\Convert-SqlServerInventoryClixmlToExcel.ps1 -FromPath "C:\Users\e001633\Documents\SQL Server Inventory - 2013-04-24-12-49.xml.gz" -ColorTheme Blue
A trick I use in Windows to make sure I'm copying the full path is to hold down the shift key, right click on the file, and choose Copy as path from the menu - that'll copy the full path (including extensions hidden by Windows Explorer) and then wrap the whole thing with double quotes.

And on that note I'll add a TODO item for the next version to handle the error a little more gracefully than barfing a bunch of PowerShell errors to the console.

Kendal

PS - try out -ColorScheme Dark ... I personally prefer that one for readability. :-)
Apr 25, 2013 at 7:02 PM

Hi Kendal,

The more I look at this it looks like that the XML file was never created. I have attached the log file.

The server that I am connected to I have admin rights.

Dave Schaeffer

Coordinator
Apr 25, 2013 at 7:29 PM
Hi Dave,
I'm not sure attachments to discussions work. If you want to email me the log file I can take a look at what's going on - kendal.vandyke@gmail.com You can also run the following code to load the GZ file and peek at what's in it (make sure to verify the path is correct before running, of course):
Import-Module -Name SqlServerInventory

$Inventory = Import-SqlServerInventoryFromGzClixml -Path "C:\Users\e001633\Documents\SQL Server Inventory - 2013-04-24-12-49.xml.gz"

# SQL Server Inventory
$Inventory| Format-List -Property StartDateUTC, EndDateUTC, DatabaseServerScanSuccessCount, DatabaseServerScanErrorCount

# Windows Inventory
$Inventory.WindowsInventory | Format-List -Property StartDateUTC, EndDateUTC, ScanSuccessCount, ScanErrorCount

# Show Names of each Database Engine Instance
$Inventory.DatabaseServer | ForEach-Object {
   $_.ServerName
}

Remove-Module -Name SqlServerInventory
Remove-Variable -Name Inventory
Kendal