trying to pass a list of servers to a parameter

I am trying to use this powershell script to pull SQL Server information and place it in a table. It works great if I pass the server names in a comma delimited list. But I need it to work by pulling the list from the CMS(Central Management Server). The output from that query is a list of servers (1 per line). It works for other scripts but not this one.

Here is the whole script but it is the last few lines I am having a problem with. [pre]

# Assume you have SQLServer PowerShell module installed

on the server where you execute this script

Import-Module sqlserver -DisableNameChecking;

function Get-SQLDBInventory

{

[cmdletbinding()]

Param( [Parameter(Mandatory=$false, ValueFromPipeline=$true)]

[Alias(“SQLServer”,“Instance”)]

[string[]]$ServerInstance = $env:computername

)

[string]$qry = @"

set nocount on;

if object_id(‘tempdb…#t’, ‘U’) is not null

drop table #t;

create table #t (

ServerName varchar(128) default @@servername

, DBName varchar(128) default db_name()

, DBOwner varchar(128)

, CreateDate datetime2

, RecoveryModel varchar(12)

, StateDesc varchar(60)

, CompatibilityLevel int

, DataFileSizeMB int

, LogFileSizeMB int

, DataUsageMB int

, IndexUsageMB int

, SizeMB decimal(17,2)

, Collation varchar(60)

, UserCount int

, RoleCount int

, TableCount int

, SPCount int

, UDFCount int

, ViewCount int

, DMLTriggerCount int

, IsCaseSensitive bit

, IsTrustWorthy bit

, LastFullBackupDate datetime2

, LastDiffBackupDate datetime2

, LastLogBackupDate datetime2);

insert into #t (DBName, DBOwner, CreateDate, RecoveryModel, StateDesc, CompatibilityLevel, IsCaseSensitive

, IsTrustWorthy, Collation, LastFullBackupDate, LastDiffBackupDate, LastLogBackupDate)

select name, suser_sname(owner_sid), create_date, recovery_model_desc, state_desc,compatibility_level

, IsCaseSensitive=CAST(CHARINDEX(N’CS’, collation_name) AS bit), is_trustworthy_on, Collation_Name

, t.LastFullBackup, t.LastDiffBackup, t.LastLogBackup

from master.sys.databases db

outer apply ( SELECT

MAX(CASE WHEN b.type = ‘D’ THEN b.backup_finish_date END) AS LastFullBackup,

MAX(CASE WHEN b.type = ‘I’ THEN b.backup_finish_date END) AS LastDiffBackup,

MAX(CASE WHEN b.type = ‘L’ THEN b.backup_finish_date END) AS LastLogBackup

FROM msdb.dbo.backupset b

where b.database_name = db.name

) t;

EXEC master.dbo.sp_msforeachdb 'use [?]

update t set SizeMB=(select sum(size)/128. from dbo.sysfiles)

, DataUsageMB=x.DataUsageMB, IndexUsageMB=x.IndexUsageMB

, DataFileSizeMB = u.DBSize, LogFileSizeMB = u.LogSize

, TableCount=y.TC, UDFCount=y.UC, SPCount = y.SC, ViewCount=y.VC

, DMLTriggerCount=y.DC

, UserCount = z.UC, RoleCount = z.RC

from #t t

outer apply (

SELECT SUM(case when df.type in (0,2,4) then df.size else 0 end)/128

, SUM(case when df.type in (1,3) then df.size else 0 end)/128

FROM sys.database_files df

) u(DBSize, LogSize)

outer apply(select DataUsageMB=sum(

CASE

When it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236) Then 0

When a.type <> 1 and p.index_id < 2 Then a.used_pages

When p.index_id < 2 Then a.data_pages

Else 0

END)/128,

IndexUsageMB=(sum(a.used_pages)-sum(

CASE

When it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236) Then 0

When a.type <> 1 and p.index_id < 2 Then a.used_pages

When p.index_id < 2 Then a.data_pages

Else 0

END

))/128

from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id

left join sys.internal_tables it on p.object_id = it.object_id

) x

outer apply

( select SC=Sum(case Type when ‘‘P’’ then 1 else 0 end)

, DC=Sum(case Type when ‘‘TR’’ then 1 else 0 end)

, TC=Sum(case Type when ‘‘U’’ then 1 end)

, UC= sum(case when Type in (’‘TF’’, ‘‘IF’’, ‘‘FN’’) then 1 else 0 end)

, VC=Sum(case Type when ‘‘V’’ then 1 else 0 end)

from sys.objects where object_id > 1024

and type in (’‘U’’,’‘P’’,’‘TR’’,’‘V’’,’‘TF’’,’‘IF’’,’‘FN’’)

) y

outer apply

( select UC = sum(case when [Type] in (’‘G’’,’‘S’’,’‘U’’) then 1 else 0 end)

, RC = sum(case when Type = ‘‘R’’ then 1 else 0 end)

from sys.database_principals

where principal_id > 4

) z where t.DBName=db_name();

SELECT * FROM #T

"@

$dt2 = new-object System.Data.DataTable;

$dt2.columns.add((new-object System.Data.DataColumn(‘ServerName’ , [System.String])));

$dt2.columns.add((new-object System.Data.DataColumn(‘DBName’ , [System.String])));

$dt2.columns.add((new-object System.Data.DataColumn(‘DBOwner’ , [System.String])));

$dt2.columns.add((new-object System.Data.DataColumn(‘CreateDate’ , [System.DateTime])));

$dt2.columns.add((new-object System.Data.DataColumn(‘RecoveryModel’ , [System.String])));

$dt2.columns.add((new-object System.Data.DataColumn(‘StateDesc’ , [System.String])));

$dt2.columns.add((new-object System.Data.DataColumn(‘CompatibilityLevel’ , [System.Int32])));

$dt2.columns.add((new-object System.Data.DataColumn(‘DataFileSizeMB’ , [System.Int32])));

$dt2.columns.add((new-object System.Data.DataColumn(‘LogFileSizeMB’ , [System.Int32])));

$dt2.columns.add((new-object System.Data.DataColumn(‘DataUsageMB’ , [System.Int32])));

$dt2.columns.add((new-object System.Data.DataColumn(‘IndexUsageMB’ , [System.Int32])));

$dt2.columns.add((new-object System.Data.DataColumn(‘SizeMB’ , [System.Decimal])));

$dt2.columns.add((new-object System.Data.DataColumn(‘Collation’ , [System.String])));

$dt2.columns.add((new-object System.Data.DataColumn(‘UserCount’ , [System.Int32])));

$dt2.columns.add((new-object System.Data.DataColumn(‘RoleCount’ , [System.Int32])));

$dt2.columns.add((new-object System.Data.DataColumn(‘TableCount’ , [System.Int32])));

$dt2.columns.add((new-object System.Data.DataColumn(‘SPCount’ , [System.Int32])));

$dt2.columns.add((new-object System.Data.DataColumn(‘UDFCount’ , [System.Int32])));

$dt2.columns.add((new-object System.Data.DataColumn(‘ViewCount’ , [System.Int32])));

$dt2.columns.add((new-object System.Data.DataColumn(‘DMLTriggerCount’ , [System.Int32])));

$dt2.columns.add((new-object System.Data.DataColumn(‘IsCaseSensitive’ , [System.Boolean])));

$dt2.columns.add((new-object System.Data.DataColumn(‘IsTrustWorthy’ , [System.Boolean])));

$dt2.columns.add((new-object System.Data.DataColumn(‘LastFullBackupDate’, [System.DateTime])));

$dt2.columns.add((new-object System.Data.DataColumn(‘LastDiffBackupDate’, [System.DateTime])));

$dt2.columns.add((new-object System.Data.DataColumn(‘LastLogBackupDate’ , [System.DateTime])));

foreach ($svr in $ServerInstance)

{ Write-verbose “processing:$svr

try {

write-host “Processing $svr -ForegroundColor Green

$dt=invoke-sqlcmd -ServerInstance $svr -Database master -Query $qry

-QueryTimeout 120 -OutputAs DataTables;

$dt2.merge($dt); #append result to $dt2

}

catch

{

$r = $dt2.NewRow()

$r.ServerName = $svr;

$r.DBName = ‘Server-Unaccessible’;

$dt2.Rows.add($r);

write-Error “Error Processing$svr ;

}

}#foreach $svr

Write-Output $dt2;

}

#####THIS WORKS GREAT PERFECTLY######

#$dt2 = Get-SQLDBInventory -serverinstance ‘server1’,‘server2’,‘server3’

#it only pulls information for the 1st server and then exits.

$allservers = Get-DbaCmsRegServer -SqlInstance localhost -Group CMS_SQLGROUP | Select-Object -Unique -ExpandProperty ServerName

$dt2 = $allservers | get-sqldbinventory

Write-SqlTableData -ServerInstance localhost -DatabaseName DBAdmin -SchemaName dbo -TableName DBInventory -InputData $dt2;

[/pre]

I don’t know what get-sqldbinventory is. Unless its computername parameter accepts its value over the pipe byvalue, piping the names to it won’t work.

The function wrapper isn’t giving a really providing a lot of value here, you are just using it as a looping mechanism. You could be able to do something as simple as this:

$qry = @"
    SELECT @@SERVERNAME AS 'ServerName'
           ,DB_NAME(dbid) AS 'Database'
           ,name, 
           ,CONVERT(BIGINT, size) * 8 AS 'size_in_kb'
           ,filename
     FROM master..sysaltfiles
"@ 

$allservers = Get-DbaCmsRegServer -SqlInstance localhost -Group CMS_SQLGROUP | Select-Object -Unique -ExpandProperty ServerName

$inventory = foreach ($srv in $allservers) {
    Invoke-SqlCmd -ServerInstance $srv -Database master -Query $qry -QueryTimeout 120 -OutputAs DataTables
}

$inventory | Write-SqlTableData -ServerInstance localhost -DatabaseName DBAdmin -SchemaName dbo -TableName DBInventory -Force

Your output is a data table and you don’t need to do any complex merges or anything that I can tell. If the temp table and destination table are the same schema, you should be able to pipe right to Write-SqlTableData. Here is a quick test:

$results = foreach ($t in 'Server1','Server2','Server3') {
    #Create Table object
    $table = New-Object system.Data.DataTable “Temp”

    #Define Columns
    $col1 = New-Object system.Data.DataColumn ColumnName1,([string])
    $col2 = New-Object system.Data.DataColumn ColumnName2,([string])

    #Add the Columns
    $table.columns.add($col1)
    $table.columns.add($col2)

    #Create a row
    $row = $table.NewRow()

    #Enter data in the row
    $row.ColumnName1 = $t
    $row.ColumnName2 = "1.0"

    #Add the row to the table
    $table.Rows.Add($row)

    $table
}

$results

Output:

PS C:\WINDOWS\system32> $results

ColumnName1 ColumnName2
----------- -----------
Server1     1.0        
Server2     1.0        
Server3     1.0     

You can also add -First to you CM Query to test if the process is working before running it on all servers:

$allservers = Get-DbaCmsRegServer -SqlInstance localhost -Group CMS_SQLGROUP | Select-Object -Unique -ExpandProperty ServerName -First 3