Write results to table on Central Management Server

  • PHXHoward

    SSCrazy Eights

    Points: 8380

    Hi everyone. Hope you are having a great day. I would love some help on this PowerShell query. What I am trying to do is run a query on all servers in my Central Management Server Group and write the server name and SQL Server version to a table on the CMS called SQLServers. When I run this .ps1 file in sqlps, I get this output for each variable "System.Data.DataRow" instead of the actual values.

    Here is the ps1 file contents:

    $serverGroupPath = 'SQLSERVER:\SQLRegistration\Central Management Server Group\test\'

    $instanceNameList = dir $serverGroupPath -recurse | where-object { $_.Mode.Equals("-") } | select-object Name -Unique

    foreach($instanceName in $instanceNameList)

    {

    $ServerResult = Invoke-Sqlcmd -query "Select @@SERVERNAME" -ServerInstance $instanceName.Name

    $VersionResult = Invoke-Sqlcmd -query "Select @@Version" -ServerInstance $instanceName.Name

    Invoke-Sqlcmd -query "Insert into SQLServers(ServerName, Version) Values('$ServerResult','$VersionResult')" -serverinstance "DBASERVER"-database "DBA_Maintenance"

    }

    ----

    Also, can you tell me if running a separate invoke-sqlcmd query to get each field variable is the most efficient way to do this? Can I collect the entire query result in one variable and write the fields to a table?

  • PHXHoward

    SSCrazy Eights

    Points: 8380

    Hi everyone. I found that if I make my variables like this:

    $ServerResult = Invoke-Sqlcmd -query "Select @@SERVERNAME" -ServerInstance $instanceName.Name | Out-String

    $VersionResult = Invoke-Sqlcmd -query "Select @@Version" -ServerInstance $instanceName.Name | Out-String

    they output to a string instead of "System.Data.DataRow"

    but now it includes the column name like this:

    Column1 ------- <server name>

    I'm wondering if there is a way to exclude the column name from the result?

    Also can both variables be combined into one variable as an array? Just not sure how to build the query,

    Thanks much.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply