Sql-server – Putting together Powershell script to document SQL Server with Instances

Ok, this is a messy one, so bear with me.

We have a PowerShell script that runs from our CMS/Master SQL Managment Server that fetches information from all servers we have, specified in a one column table ([SQLServerInformation].[dbo].[server_list]). The script will then go through the list with a foreach loop and query the servers and write whatever information it receives back into another table in the same database ([SQLServerInformaton].[dbo].[$ServerName_server_trunc], so the table name will be whatever server was just queried in the loop. E.g. [sqlserver01_server_trunc] or [managementsql11_server_trunc]).

The issue I'm having is that I'm also trying to extract what instances are running on the server. The instances will always run with the same SQL Server version, so I don't need any advanced script that will go through the registry (which I've read is not a good idea). The script (which works completely fine without the instance related bits) I've created so far is this:

# We store all SQL Servers in this table. Here we fetch them into a variable.
$QueryFetchServerNames = "SELECT ServerName AS Name FROM [SQLServerInformation].[dbo].[server_list]"

# Make sure we don't run into the "System.Data.DataRow" problem.
$ServerName = (Invoke-Sqlcmd -ServerInstance CBSWSQLMGT01 -Query $QueryFetchServerNames).Name

foreach ($Server in $ServerName) {
    # Fetch the instances under the server.
    $Instances = Get-ChildItem -Path "SQLSERVER:\SQL\$Server"

    Invoke-Sqlcmd -ServerInstance $Server -Query "
        SELECT SERVERPROPERTY('MachineName') AS [Server], 
        "$Instances | foreach-object { $_ }" AS [Instance(s)],
           (SELECT Server_type = CASE 
                                   WHEN virtual_machine_type = 1 THEN 'Virtual' 
                                   ELSE 'Physical' 
                                 END 
            FROM   sys.dm_os_sys_info) AS [Virtual/Physical], 
           (SELECT ( cpu_count / hyperthread_ratio ) 
            FROM   sys.dm_os_sys_info) AS [CPU(s)], 
           (SELECT CASE 
                     WHEN hyperthread_ratio = cpu_count THEN cpu_count 
                     WHEN ( ( cpu_count - hyperthread_ratio ) / ( 
                            cpu_count / hyperthread_ratio ) ) 
                          = 0 THEN 1 
                   END 
            FROM   sys.dm_os_sys_info) AS [Core(s)], 
           (SELECT ( [total_physical_memory_kb] / 1024 ) AS [RAM (MB)] 
            FROM   [master].[sys].[dm_os_sys_memory]) AS [RAM (MB)], 
           LEFT(@@VERSION, Charindex(')', @@VERSION)) AS [Version], 
           SERVERPROPERTY('ProductVersion') AS [Build], 
           SERVERPROPERTY('Edition') AS [Edition];"| 

           # Write it all into a table.
           Write-SqlTableData -ServerInstance CBSWSQLMGT01 -DatabaseName SQLServerInformation -SchemaName dbo -TableName $ServerName_server_trunc -Force
}

I get the output:

PS D:\PowerShell\ServerInformation\cbswsqlmgt01> D:\PowerShell\ServerInformation\cbswsqlmgt01\test.ps1
Invoke-Sqlcmd : A positional parameter cannot be found that accepts argument 'System.Object[]'.
At D:\PowerShell\ServerInformation\cbswsqlmgt01\test.ps1:30 char:5
+     Invoke-Sqlcmd -ServerInstance $Server -Query "
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (:) [Invoke-Sqlcmd], ParameterBindingException
    + FullyQualifiedErrorId : PositionalParameterNotFound,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
 
Invoke-Sqlcmd : A positional parameter cannot be found that accepts argument '[CBSWSQLMGT01]'.
At D:\PowerShell\ServerInformation\cbswsqlmgt01\test.ps1:30 char:5
+     Invoke-Sqlcmd -ServerInstance $Server -Query "
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (:) [Invoke-Sqlcmd], ParameterBindingException
    + FullyQualifiedErrorId : PositionalParameterNotFound,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

If I run just this, the instances will display fine:

# We store all SQL Servers in this table. Here we fetch them into a variable.
$QueryFetchServerNames = "SELECT ServerName AS Name FROM [SQLServerInformation].[dbo].[server_list]"

# Make sure we don't run into the "System.Data.DataRow" problem.
$ServerName = (Invoke-Sqlcmd -ServerInstance CBSWSQLMGT01 -Query $QueryFetchServerNames).Name

foreach ($Server in $ServerName) {
    # Fetch the instances under the server.
    $Instances = Get-ChildItem -Path "SQLSERVER:\SQL\$Server"
    $Instances | foreach-object { $_ }
    Write-Host "--------"
}

PS D:\PowerShell\ServerInformation\cbswsqlmgt01> D:\PowerShell\ServerInformation\cbswsqlmgt01\test.ps1

Instance Name                                                                   
-------------                                                                   
ATLASSIAN                                                                       
COGNOS                                                                          
DEFAULT                                                                         
METAFORCE                                                                       
NAVISION                                                                        
PIVOTAL                                                                         
QUANTUM                                                                         
ZERVICEPOINT                                                                    
--------
DEFAULT                                                                         
--------

So, my question is, how can I print out the instances of the server, and at the same time, write them to the database table [SQLServerInformation].[dbo].[$ServerName_server_trunc]?

Solution by Scott Hodgin:

# We store all SQL Servers in this table. Here we fetch them into a variable.
$ServerName = (Invoke-Sqlcmd -ServerInstance CBSWSQLMGT01 -Query "SELECT ServerName AS Name FROM [SQLServerInformation].[dbo].[server_list]").Name

foreach ($Server in $ServerName) {
    # Fetch the instances under the server.
    $Instances = Get-ChildItem -Path "SQLSERVER:\SQL\$Server"
    $InstanceString = ($Instances.Name -join "`r`n" | Out-String).Trim()
    $InstanceString = $InstanceString.Replace("$Server`r`n","MSSQLSERVER`r`n")
    $InstanceString = $InstanceString.Replace("$Server\","")

    Invoke-Sqlcmd -ServerInstance CBSWSQLMGT01 -Query "
        TRUNCATE TABLE [SQLServerInformation].[dbo].[$Server`_server]
    "

    Invoke-Sqlcmd -ServerInstance $Server -Query "
        SELECT SERVERPROPERTY('MachineName') AS [Server], 
    '$InstanceString' AS [Instance(s)],
           (SELECT Server_type = CASE 
                                   WHEN virtual_machine_type = 1 THEN 'Virtual' 
                                   ELSE 'Physical' 
                                 END 
            FROM   sys.dm_os_sys_info) AS [Virtual/Physical], 
           (SELECT ( cpu_count / hyperthread_ratio ) 
            FROM   sys.dm_os_sys_info) AS [CPU(s)], 
           (SELECT CASE 
                     WHEN hyperthread_ratio = cpu_count THEN cpu_count 
                     WHEN ( ( cpu_count - hyperthread_ratio ) / ( 
                            cpu_count / hyperthread_ratio ) ) 
                          = 0 THEN 1 
                   END 
            FROM   sys.dm_os_sys_info) AS [Core(s)], 
           (SELECT ( [total_physical_memory_kb] / 1024 ) AS [RAM (MB)] 
            FROM   [master].[sys].[dm_os_sys_memory]) AS [RAM (MB)], 
           LEFT(@@VERSION, Charindex(')', @@VERSION)) AS [Version], 
           SERVERPROPERTY('ProductVersion') AS [Build], 
           SERVERPROPERTY('Edition') AS [Edition];"| 

           # Write it all into a table.
           Write-SqlTableData -ServerInstance CBSWSQLMGT01 -DatabaseName SQLServerInformation -SchemaName dbo -TableName $Server`_server -Force
}