Unable to insert PowerShell variables from array into Invoke-Sqlcmd call

Question

I have an array that loops through a string assigning values to elements. If I then assign those elements to a variable and pass that variable into the invoke-sqlcmd I get an error. However, if I assign the value directly to the variable and pass it in it works fine:

Assign value to array:

for ($i=0; $i -lt $somearray.length; $i++) {
    $somearray[$i] = $somearray[$i].Replace('$(query)', $query.text)
}

Lets say somearray[0] = "select * from DB". We'll assign to a variable:

$query = somearray[0]

Now I pass this to my Invoke-Sqlcmd:

Invoke-Sqlcmd -Query $query -Database "local" -ServerInstance "somedb" |
    Export-Csv ".\somefile.csv"

This fails:

Invoke-Sqlcmd : Could not find stored procedure 'select * from ALERTS'.
At C:\Migration\ExportTool\ExportTool\Gavs.ps1:95 char:17
+ ...             Invoke-Sqlcmd -Query $query -Database $db -ServerInstance ...

However, if I do this:

$query = "select * from DB"

Invoke-Sqlcmd -Query $query -Database "local" -ServerInstance "somedb" |
    Export-Csv ".\somefile.csv"

it works perfectly.

Answer 1

So the reason appears to be, if you do this:

$query = somearray[0]

When you view $query in the Invoke-sql command it looks like this:

"select * from DB"

And when you do this:

$query = "select * from DB"

It looks like this:

select * from DB

So directly referencing a string removes the quotes in the Invoke-sqlcmd command. Seems very odd.

Hope this helps someone in the future.