Unable to fetch database config info

Sep 16, 2016 at 3:23 PM
Edited Sep 16, 2016 at 6:34 PM
Hi Kendal,

Thanks for the awesome scripts provided to SQL Community.

Have a quick question. I have run the scripts using below command line but I was unable to capture Database info. I can data collected for windows OS info but for sql server database file info, filegroups, sizes etc.. was not captured in Excel.

.\Get-SqlServerInventoryToClixml.ps1 -ComputerName $env:COMPUTERNAME -DirectoryPath "D:\Inventory" -LoggingPreference verbose –Zip

This is what I see from the log file.

2016-09-16 05:05:42.2634 $ [xxxxxxxxxxxxxxx] Gathering Database information
2016-09-16 05:24:14.0540 + [xxxxxxxxxxxxxxx] Error gathering Database information: The following exception occurred while trying to enumerate the collection: "An exception occurred while executing a Transact-SQL statement or batch.". (SqlServerDatabaseEngineInformation.psm1 line 10972, char 8)

Additional info

The windows user used to run the script is a member of Windows Adminstrator Group as well as member of sysadmin role at SQL Server level.

Any suggestions to get rid of these errors ?

Thanks in advance.
Coordinator
Sep 19, 2016 at 8:41 PM
What version of SQL Server are you collecting information from and what version of SMO do you have on the machine where the scripts are running?

Kendal
Sep 20, 2016 at 7:06 PM
Hi Kendal,

Thanks for the response.

SQL 2014 SP1 Enterprise Edition. Unsure of getting SMO version. Is there any command to get SMO version?
Coordinator
Sep 26, 2016 at 7:07 PM
M1981 wrote:
Is there any command to get SMO version?
Try this in a PowerShell console:
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')
Oct 7, 2016 at 8:02 AM
Kendal, below is output.

GAC Version Location

True v2.0.50727 C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SMO\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SMO.dll

I have run SQLPowerdoc on a machine where I am local windows admin + sqladmin. I was able to successfully run it for INST1 but for INST2 it was failing with above error. Also to add, both are SQL 2014 SP1 instances.

Not sure where why I am getting errors.
Oct 12, 2016 at 2:57 PM
Hi Kendal,

Any update for me?
Coordinator
Oct 12, 2016 at 11:01 PM
You're loading the 2014 SMO assemblies (which matches the 2014 server you're trying to gather information from) so that's not the issue.

It think the query that's failing is on retrieving information about users in the database. I need to find out more to reproduce and fix. Can you run the following in a PowerShell ISE window for me (make sure to change the value for $InstanceName on line 1)? If it doesn't return any errors then the issue isn't where I think it is. If it does then we need to figure out what's special about the users in the database that's failing.
# Change the value of this variable to match the computer that you're getting the error from
$InstanceName = $env:COMPUTERNAME


# Load SMO assembly, and if we're running SQL 2008 DLLs or higher load the SMOExtended and SQLWMIManagement libraries
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | ForEach-Object {
    $SmoMajorVersion = $_.GetName().Version.Major
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLEnum') | Out-Null
    if ($SmoMajorVersion -ge 10) {
        [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | Out-Null
        [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement') | Out-Null
    }
}

# Now check which SMO assemblies are loaded and set $SmoMajorVersion to the lowest version
[System.AppDomain]::CurrentDomain.GetAssemblies() | Where-Object { $_.FullName -ilike 'Microsoft.SqlServer.SMO, Version=*' } | ForEach-Object {
    if ($_.GetName().Version.Major -lt $SmoMajorVersion) {
        $SmoMajorVersion = $_.GetName().Version.Major
    }
}


$Server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $InstanceName
$Server.ConnectionContext.Connect()

$Server.SetDefaultInitFields($true)

$Server.Databases |
Where-Object {
    $_.IsAccessible
} |
ForEach-Object {
    
    Write-Output $('Database: {0}' -f $_.Name)
    
    $_.Users | 
    ForEach-Object {
        try {
            $DbUser = New-Object -TypeName psobject -Property @{
                Name = $_.Name # System.String Name {get;set;}
                ID = $_.ID # System.Int32 ID {get;}
                Sid = if ($_.Sid) { [System.BitConverter]::ToString($_.Sid) } else { $null } # System.Byte[] Sid {get;} 
                IsSystemObject = $_.IsSystemObject # System.Boolean IsSystemObject {get;}
                HasDbAccess = $_.HasDBAccess # System.Boolean HasDBAccess {get;}

                # AuthenticationType introduced in SQL 2012
                AuthenticationType = if ($_.AuthenticationType) { $_.AuthenticationType.ToString() } else { $null } # Microsoft.SqlServer.Management.Smo.AuthenticationType AuthenticationType {get;}
                LoginType = $_.LoginType # Microsoft.SqlServer.Management.Smo.LoginType LoginType {get;}
                UserType = $_.UserType # Microsoft.SqlServer.Management.Smo.UserType UserType {get;set;}

                Login = $_.Login # System.String Login {get;set;}
                Certificate = $_.Certificate # System.String Certificate {get;set;}
                AsymmetricKey = $_.AsymmetricKey # System.String AsymmetricKey {get;set;}
                DefaultSchema = $_.DefaultSchema # System.String DefaultSchema {get;set;}

                # DefaultLanguage introduced in SQL 2012
                DefaultLanguage = if ($_.DefaultLanguage) { $_.DefaultLanguage.Name } else { $null } # Microsoft.SqlServer.Management.Smo.DefaultLanguage DefaultLanguage {get;}
                CreateDate = $_.CreateDate # System.DateTime CreateDate {get;}
                DateLastModified = $_.DateLastModified # System.DateTime DateLastModified {get;}                                        
            }    
        }
        catch {
            throw
        }
    }
}
$Server.ConnectionContext.Disconnect()

Remove-Variable -Name Server,InstanceName,DbUser,SmoMajorVersion
Oct 13, 2016 at 8:47 PM
Edited Oct 13, 2016 at 8:48 PM
Thanks for the reply.
Before that, I ran the test run once again, I have noticed that below tabs are NOT getting populated.
SQL Server Inventory - 2016-10-13-11-56 - Database Engine Config.xlsx

Database Overview
DB Config - General
DB Config - Files
DB Config - Filegroups
DB Config - Options
DB Config - AlwaysOn
DB Config - Change Tracking
DB Config - Permissions
DB Config - Mirroring
DB Security - Users
DB Security - Database Roles
DB Security - Application Roles
DB Security - Schemas
DB Security - Asymmetric Keys
DB Security - Certificates
DB Security - Symmetric Keys


complete log :

2016-10-13 11:58:18.3973 ? Starting Script: C:\Users\rio\Documents\WindowsPowerShell\Get-SqlServerInventoryToClixml.ps1
2016-10-13 11:58:18.6972 ? Beginning SQL Server Inventory
2016-10-13 11:58:18.7123 ? -LoggingPreference: verbose
2016-10-13 11:58:18.7222 ? -ComputerName: testbox
2016-10-13 11:58:18.7323 ? -MaxConcurrencyThrottle: 16
2016-10-13 11:58:18.7372 ? -PrivateOnly: False
2016-10-13 11:58:18.8672 ? -ParentProgressId: -1
2016-10-13 11:58:18.8722 ? -IncludeDatabaseObjectPermissions: False
2016-10-13 11:58:18.8773 ? -IncludeDatabaseObjectInformation: False
2016-10-13 11:58:18.8822 ? -IncludeDatabaseSystemObjects: False
2016-10-13 11:58:19.0122 ? Beginning network scan
2016-10-13 11:58:19.0172 ? -ComputerName: testbox
2016-10-13 11:58:19.0172 ? -PrivateOnly: False
2016-10-13 11:58:19.0222 ? -MaxConcurrencyThrottle: 16
2016-10-13 11:58:19.0222 ? -ResolveAliases: True
2016-10-13 11:58:19.0372 ? Resolving IP address for testbox
2016-10-13 11:58:19.3572 ? Testing PING connectivity to 1 addresses
2016-10-13 11:58:19.4622 $ Testing PING connectivity to CO1wewqe.wewq.qwe.wew.COM (10.xxx.xx.xx) [1 of 1]
2016-10-13 11:58:21.8923 $ PING response from CO1wewqe.wewq.qwe.wew.COM (10.xxx.xx.xx): True
2016-10-13 11:58:22.2422 $ PING connectivity test complete
2016-10-13 11:58:22.3022 ? Testing WMI connectivity to 1 addresses
2016-10-13 11:58:22.3876 $ Testing WMI connectivity to CO1wewqe.wewq.qwe.wew.COM (10.xxx.xx.xx) [1 of 1]
2016-10-13 11:58:22.7172 $ WMI response from CO1wewqe.wewq.qwe.wew.COM (10.xxx.xx.xx): True
2016-10-13 11:58:23.0322 $ WMI connectivity test complete
2016-10-13 11:58:23.1372 ? Network scan complete
2016-10-13 11:58:23.1473 ? -IP Addresses Scanned: 1
2016-10-13 11:58:23.1522 ? -PING Replies: 1
2016-10-13 11:58:23.1572 ? -WMI Replies: 1
2016-10-13 11:58:23.1872 ? Beginning SQL Service discovery scan
2016-10-13 11:58:23.4722 ? Scanning CO1wewqe.wewq.qwe.wew.COM at IP address 10.xxx.xx.xx for SQL Services [Device 1 of 1]
2016-10-13 11:58:28.8127 ? Found SQL Server Integration Services default instance CO1wewqe.wewq.qwe.wew.COM at IP address
2016-10-13 11:58:28.8177 ? Found SQL Full-text Filter Daemon Launcher default instance CO1wewqe.wewq.qwe.wew.COM at IP address
2016-10-13 11:58:28.8177 ? Found SQL Server default instance CO1wewqe.wewq.qwe.wew.COM at IP address
2016-10-13 11:58:28.8227 ? Found SQL Server Browser default instance CO1wewqe.wewq.qwe.wew.COM at IP address
2016-10-13 11:58:28.8227 ? Found SQL Server Agent default instance CO1wewqe.wewq.qwe.wew.COM at IP address
2016-10-13 11:58:29.1827 ? SQL Server service discovery complete
2016-10-13 11:58:29.1927 ? -SQL Full-text Filter Daemon Launcher Instance Count: 1
2016-10-13 11:58:29.1977 ? -SQL Server Instance Count: 1
2016-10-13 11:58:29.1977 ? -SQL Server Agent Instance Count: 1
2016-10-13 11:58:29.2027 ? -SQL Server Browser Instance Count: 1
2016-10-13 11:58:29.2027 ? -SQL Server Integration Services Instance Count: 1
2016-10-13 11:58:29.2177 ? Beginning scan of 1 instance(s)
2016-10-13 11:58:29.5527 ? Gathering information from CO1wewqe.wewq.qwe.wew.COM [Instance 1 of 1]
2016-10-13 11:59:35.8721 $ [CO1wewqe.wewq.qwe.wew.COM] Gathering Server service information
2016-10-13 11:59:37.4121 $ [CO1wewqe.wewq.qwe.wew.COM] Gathering Server configuration information
2016-10-13 11:59:41.1222 $ [CO1wewqe.wewq.qwe.wew.COM] Gathering Database information
2016-10-13 12:10:01.9180 + [CO1wewqe.wewq.qwe.wew.COM] Error gathering Database information: The following exception occurred while trying to enumerate the collection: "An exception occurred while executing a Transact-SQL statement or batch.". (SqlServerDatabaseEngineInformation.psm1 line 10972, char 8)
2016-10-13 12:10:01.9226 $ [CO1wewqe.wewq.qwe.wew.COM] Gathering Server security information
2016-10-13 12:10:11.5978 $ [CO1wewqe.wewq.qwe.wew.COM] Gathering Endpoint information
2016-10-13 12:10:11.9728 $ [CO1wewqe.wewq.qwe.wew.COM] Gathering Server linked server information
2016-10-13 12:10:12.4378 $ [CO1wewqe.wewq.qwe.wew.COM] Gathering Server startup procedure information
2016-10-13 12:10:12.7478 $ [CO1wewqe.wewq.qwe.wew.COM] Gathering Server Trigger information
2016-10-13 12:10:12.8478 $ [CO1wewqe.wewq.qwe.wew.COM] Gathering Resource Governor information
2016-10-13 12:10:13.3478 $ [CO1wewqe.wewq.qwe.wew.COM] Gathering SQL Trace information
2016-10-13 12:10:14.0428 $ [CO1wewqe.wewq.qwe.wew.COM] Gathering Trace Flag information
2016-10-13 12:10:14.0978 $ [CO1wewqe.wewq.qwe.wew.COM] Gathering Database Mail information
2016-10-13 12:10:17.4428 $ [CO1wewqe.wewq.qwe.wew.COM] Gathering SQL Agent service information
2016-10-13 12:10:17.6578 $ [CO1wewqe.wewq.qwe.wew.COM] Gathering SQL Agent configuration information
2016-10-13 12:10:18.0278 $ [CO1wewqe.wewq.qwe.wew.COM] Gathering SQL Agent job information
2016-10-13 12:10:38.0830 $ [CO1wewqe.wewq.qwe.wew.COM] Gathering SQL Agent alert information
2016-10-13 12:10:38.1280 $ [CO1wewqe.wewq.qwe.wew.COM] Gathering SQL Agent operator information
2016-10-13 12:10:38.9580 ? Scanned CO1wewqe.wewq.qwe.wew.COM with 1 errors
2016-10-13 12:10:39.3330 ? Instance scan complete (Success: 1; Failure: 0)
2016-10-13 12:10:39.5330 ? Beginning network scan
2016-10-13 12:10:39.5330 ? -ComputerName: CO1wewqe.wewq.qwe.wew.COM
2016-10-13 12:10:39.5380 ? -PrivateOnly: False
2016-10-13 12:10:39.5380 ? -MaxConcurrencyThrottle: 16
2016-10-13 12:10:39.5380 ? -ResolveAliases: False
2016-10-13 12:10:39.5430 ? Resolving IP address for CO1wewqe.wewq.qwe.wew.COM
2016-10-13 12:10:39.7480 ? Testing PING connectivity to 1 addresses
2016-10-13 12:10:39.8030 $ Testing PING connectivity to CO1wewqe.wewq.qwe.wew.COM (10.xxx.xx.xx) [1 of 1]
2016-10-13 12:10:42.0730 $ PING response from CO1wewqe.wewq.qwe.wew.COM (10.xxx.xx.xx): True
2016-10-13 12:10:42.3580 $ PING connectivity test complete
2016-10-13 12:10:42.4030 ? Testing WMI connectivity to 1 addresses
2016-10-13 12:10:42.4430 $ Testing WMI connectivity to CO1wewqe.wewq.qwe.wew.COM (10.xxx.xx.xx) [1 of 1]
2016-10-13 12:10:42.7031 $ WMI response from CO1wewqe.wewq.qwe.wew.COM (10.xxx.xx.xx): True
2016-10-13 12:10:43.0081 $ WMI connectivity test complete
2016-10-13 12:10:43.1231 ? Network scan complete
2016-10-13 12:10:43.1281 ? -IP Addresses Scanned: 1
2016-10-13 12:10:43.1281 ? -PING Replies: 1
2016-10-13 12:10:43.1331 ? -WMI Replies: 1
2016-10-13 12:10:43.1431 ? Beginning machine scan
2016-10-13 12:10:43.4331 ? Scanning testbox on IP address 10.xxx.xx.xx [Machine 1 of 1]
2016-10-13 12:13:28.7592 $ [10.xxx.xx.xx] Gathering computer system information
2016-10-13 12:13:28.8742 $ [10.xxx.xx.xx] Gathering computer system product information
2016-10-13 12:13:28.9142 $ [10.xxx.xx.xx] Gathering OS information
2016-10-13 12:13:29.0542 $ [10.xxx.xx.xx] Gathering time zone
2016-10-13 12:13:29.0842 $ [10.xxx.xx.xx] Gathering pagefile information
2016-10-13 12:13:29.1742 $ [10.xxx.xx.xx] Gathering network adapter configuration
2016-10-13 12:13:29.2692 $ [10.xxx.xx.xx] Gathering information about physical memory
2016-10-13 12:13:29.4842 $ [10.xxx.xx.xx] Gathering processor information
2016-10-13 12:13:29.5792 $ [10.xxx.xx.xx] Gathering system enclosure information
2016-10-13 12:13:29.6092 $ [10.xxx.xx.xx] Gathering CD-ROM Information
2016-10-13 12:13:29.6692 $ [10.xxx.xx.xx] Gathering disk information
2016-10-13 12:13:31.4942 $ [10.xxx.xx.xx] Gathering BIOS information
2016-10-13 12:13:31.5542 $ [10.xxx.xx.xx] Gathering local groups and group members
2016-10-13 12:13:32.2942 $ [10.xxx.xx.xx] Gathering local users
2016-10-13 12:13:32.7592 $ [10.xxx.xx.xx] Gathering information about logged on users
2016-10-13 12:13:32.9692 + [10.xxx.xx.xx] Error gathering information about logged on users: Exception calling "SizeOf" with "1" argument(s): "Type 'System.RuntimeType' cannot be marshaled as an unmanaged structure; no meaningful size or offset can be computed." (RDS-Manager.psm1 line 601, char 9)
2016-10-13 12:13:32.9692 + [10.xxx.xx.xx] Reverting to Win32_Process to determine logged on users
2016-10-13 12:13:35.1443 $ [10.xxx.xx.xx] Gathering IPv4 Route information
2016-10-13 12:13:35.3542 $ [10.xxx.xx.xx] Gathering event log settings
2016-10-13 12:13:35.5042 $ [10.xxx.xx.xx] Gathering information about power plans
2016-10-13 12:13:35.5692 $ [10.xxx.xx.xx] Gathering printer information
2016-10-13 12:13:35.6992 $ [10.xxx.xx.xx] Gathering process information
2016-10-13 12:14:45.2897 $ [10.xxx.xx.xx] Gathering registry size information
2016-10-13 12:14:45.3147 $ [10.xxx.xx.xx] Gathering information about services
2016-10-13 12:14:46.7397 $ [10.xxx.xx.xx] Gathering information about shares
2016-10-13 12:14:46.9547 $ [10.xxx.xx.xx] Gathering Sound Device information
2016-10-13 12:14:46.9747 $ [10.xxx.xx.xx] Gathering TapeDrive information
2016-10-13 12:14:46.9947 $ [10.xxx.xx.xx] Gathering Video Controller information
2016-10-13 12:14:47.0347 $ [10.xxx.xx.xx] Gathering Startup Commands information
2016-10-13 12:14:48.2597 $ [10.xxx.xx.xx] Gathering application information from WMI
2016-10-13 12:15:37.0200 $ [10.xxx.xx.xx] Gathering application information from registry
2016-10-13 12:15:37.4750 $ [10.xxx.xx.xx] Gathering information about patches from WMI
2016-10-13 12:15:41.1950 $ [10.xxx.xx.xx] Gathering information about patches from registry
2016-10-13 12:15:41.7250 ? Scanned t
Oct 13, 2016 at 8:55 PM
Edited Oct 14, 2016 at 6:35 AM
When I ran the script provided by you in Powershell ISE , I get below output.


Remove-Variable -Name Server,InstanceName,DbUser,SmoMajorVersion
Database: Auditdb
Database: Alerting
Database: Config
Database: master
Database: model
Database: msdb
Database: SSISDB
The following exception occurred while trying to enumerate the collection: "An exception occurred while executing a
Transact-SQL statement or batch.".
At line:36 char:5
  • $_.Users |
  • ~~~~~~~~~~
    • CategoryInfo : NotSpecified: (:) [], ExtendedTypeSystemException
    • FullyQualifiedErrorId : ExceptionInGetEnumerator
Database: tempdb

PS C:\Users\rio>
Oct 17, 2016 at 7:18 AM
Edited Oct 17, 2016 at 10:06 AM
Sir any updates for me.
Oct 21, 2016 at 6:31 PM
Hi Kendal,

Any updates?