title: PowerShell Invoke-Sqlcmd with Array item as variable in WHERE
body: <p>I'm not getting any output from this script. Not even an error msg... I think my problem is the use of a variable in the WHERE clause. I've tried escaping this $(item) with backtics but making no progress. Can anyone help me understand what I'm doing wrong?</p>
  
  <pre><code>$dbserver   = "AHDC389"
  $dbase  = "FOO"
  $table  = "$($dbase).dbo.BAR"
  
  Import-Module “sqlps” -DisableNameChecking
  
  $myArray    = @(Invoke-Sqlcmd "Select myColumn From adifferentserver.dbo.[mylookuptable]") | select-object -expand myColumn
  
  ForEach ($item in $myyArray) {
  
      Invoke-Sqlcmd "Select * FROM $table Where FacilityID='$(item)' "
  
  }
  </code></pre>
  
  <p>UPDATE:</p>
  
  <p>So when I modify the script like below, is seems the variable for the Where= is not expanding out.</p>
  
  <pre><code>$dbserver   = "AHDC389"
  $dbase  = "FOO"
  $table  = "$($dbase).dbo.BAR"
  
  Import-Module “sqlps” -DisableNameChecking
  
  $myArray    = @(Invoke-Sqlcmd "Select myColumn From adifferentserver.dbo.[mylookuptable]") | select-object -expand myColumn
  
  $myQuery    = "Select * FROM {0} Where FacilityID='{1}'" -f $table, $item
  
  ForEach ($item in $mArray) {
  
      Write-Host $myQuery
      #Invoke-Sqlcmd $myQuery
  
  }
  </code></pre>
  
  <p>The value of $myQuery is returned to the console indicating the $item is not being expanded:</p>
  
  <pre><code>Select * FROM  FOO.dbo.BAR Where Facility=''
  </code></pre>