Write results to table on Central Management Server

• PHXHoward

  SSCrazy Eights

  Points: 8389

  More actions

  December 25, 2010 at 12:16 pm

  #232715

  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: 8389

  More actions

  December 25, 2010 at 12:49 pm

  #1266279

  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.