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.

somearray[0] -> $somearray[0]?

Ansgar Wiechers
– Ansgar Wiechers

2019-01-10 12:07:46 +00:00
Commented Jan 10, 2019 at 12:07 — Ansgar Wiechers
– Ansgar Wiechers, Commented Jan 10, 2019 at 12:07

hoakey · Accepted Answer · 2019-01-11 11:23:17Z

0

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.

answered Jan 11, 2019 at 11:23

hoakey

9983 gold badges18 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related