0

I have the below script, which runs an SQL query for each server in the list. I want it to add each SQL result to an array, and then once the ForEach loop is finished, I want it to print out the array as a formatted table.

At the moment, the formatted table is only showing me the results from the first server, my problem is making it work to show the results for all the servers.

$ServerNames=@('SQL1,6435'
              ,'SQL2,6431'
              ,'SQL3,6434'
              ,'SQL4,6438'
              ,'SQL5,6439')

$VersionQuery = "SELECT SERVERPROPERTY ('ServerName') [ServerName]`
                      , SERVERPROPERTY ('Edition') [Edition]`
                      , SERVERPROPERTY ('ProductLevel') [ProductLevel]`
                      , SERVERPROPERTY ('ProductUpdateLevel') [ProductUpdateLevel]`
                      , SERVERPROPERTY ('ProductBuildType') [ProductBuildType]`
                      , SERVERPROPERTY ('ProductUpdateReference') [ProductUpdateReference]`
                      , SERVERPROPERTY ('ProductVersion') [ProductVersion]"

$Results = foreach($ServerName in $ServerNames)
{
    Invoke-Sqlcmd2 -Query $VersionQuery -QueryTimeout 5 -ConnectionTimeout 5 -ServerInstance $ServerName -ErrorAction Stop
}

$Results | ConvertTo-DbaDataTable | Format-Table

Can anyone guide me on achieving this?

Update: Removing ConvertTo-DbaDataTable fixed the problem.

mklement0
  • 382,024
  • 64
  • 607
  • 775
Jeffrey
  • 2,095
  • 3
  • 20
  • 36
  • 1
    I realised it was as simple as just removing ConvertTo-DbaDataTable, If I just pipe it straight to Format-Table it works and gives me the desired results. And thanks for your suggestion on doing $Result = foreach(..., I forgot I could do this. – Jeffrey Feb 08 '19 at 14:20
  • Hmm... that suggests that the linked question isn't actually a duplicate, especially given that by definition all your `Invoke-Sqlcmd2` return objects with the _same_ properties. While I can see that `ConvertTo-DbaDataTable` was never necessary, the question is why it produced the symptom. Does `Invoke-Sqlcmd2` return its result collection as _single objects_ to the pipeline? Given that you've edited out the `ConvertTo-DbaDataTable` call, the question is now confusing, since the code doesn't exhibit a problem anymore (I've fixed that). – mklement0 Feb 08 '19 at 16:18
  • 1
    I suggest either deleting the question or posting your own answer that explains why introducing `ConvertTo-DbaDataTable` caused the problem. – mklement0 Feb 08 '19 at 16:19

0 Answers0