invoke-sqlcmd use column names within the script

Welcome Forums General PowerShell Q&A invoke-sqlcmd use column names within the script

Viewing 3 reply threads
  • Author
    Posts
    • #211488
      Participant
      Topics: 1
      Replies: 1
      Points: 14
      Rank: Member

      [SOLVED]I use invoke-sqlcmd extensively and am building automation that takes SQL statements from a config file, executes them and produces various email alerts.

      In the script, I need to dynamically reference the column names so I can plug various fields into the body of the message. Using invoke-sqlcmd, how can I load those column names into a variable (array or hashtable) so that I can reference them within the script?

      [MY SOLUTION]

      Basically, you can take the results of invoke-sqlcmd and get the column names using the following:

      $ColumnsHeaders = @()

      $Results = invoke-sqlcmd -server -query “select * from table…”

      $ColumnCount = $Results[0].itemarray.count

      For ($Ctr = 0; $Ctr -lt $ColumnCount;  $Ctr++) {

      $ColumnHeaders += $Results[0].table.columns[$Ctr].caption

      }

      • This topic was modified 2 months, 1 week ago by doughorton12. Reason: Updated with solution
      • This topic was modified 2 months, 1 week ago by doughorton12.
      • This topic was modified 2 months, 1 week ago by doughorton12.
    • #211494
      Participant
      Topics: 12
      Replies: 523
      Points: 1,214
      Helping Hand
      Rank: Community Hero
      $ColumnNames = @('Green','Blue','Yellow')
      
      #region some script here
      
      'Referencing the first column - the 0 element of the $ColumnNames array'
      $ColumnNames[0]
      
      #endregion
      
      • This reply was modified 2 months, 1 week ago by Sam Boutros.
    • #211497
      Participant
      Topics: 1
      Replies: 1
      Points: 14
      Rank: Member
      PowerShell
      9 lines

      <textarea class=”ace_text-input” style=”opacity: 0; height: 18px; width: 6.59781px; left: 44px; top: 0px;” spellcheck=”false” wrap=”off”></textarea>

      1
      2
      3
      4
      5
      6
      7
      8
      9
      $ColumnNames = @(‘Green’,‘Blue’,‘Yellow’)
      #region some script here
      ‘Referecing the first column – the 0 element of the $ColumnNames array’
      $ColumnNames[0]
      #endregion
      XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

      I appreciate the response, but your code assumes that we know the column names in advance. I actually figured out how to do it and will post my solution shortly.

    • #211539
      Participant
      Topics: 12
      Replies: 1622
      Points: 2,560
      Helping Hand
      Rank: Community Hero

      This is possible to do dynamically as originally requested:

      PS C:\Users\rasim> 
      $sqlResult = @()
      $sqlResult += [pscustomobject]@{FirstName = 'John';LastName='Smith';Hobby='Curling'}
      $sqlResult += [pscustomobject]@{FirstName = 'Sally';LastName='Johnson';Hobby='Monopoly'}
      
      $sqlResult
      
      
      FirstName LastName Hobby   
      --------- -------- -----   
      John      Smith    Curling 
      Sally     Johnson  Monopoly
      
      
      #Take first row and get the PSOBject property names
      PS C:\Users\rasim> $sqlResult[0].PSObject.Properties.Name
      FirstName
      LastName
      Hobby
      
Viewing 3 reply threads
  • You must be logged in to reply to this topic.