How do I use a variable in an SQL script passed in from a powershell script

Question

I'm new to Powershell and getting stuck with some of the syntax. I have a powershell script that calls a SQL script with a variable I want to access inside the script.

param (
[string] $inst = "all"
)

invoke-sqlcmd -inputfile "D:\PowershellScripts\testSQL\testScript.sql" -variable $powerShellVar=$inst -serverinstance '.\sql2008';

in my SQL query I want to be able to access the $powerShellVar variable like this

print $powerShellVar

However, when I do that, I get this error

Invoke-Sqlcmd : Invalid pseudocolumn "$powerShellVar"

What am I doing wrong?

Answer 1

The parameter syntax is invalid. No wonder, as this is poorly documented. The -variable parameter assumes an array that contains name/value pairs like so,

$sqlParameters = @("variable1 = 'value1'", "variable2 = 'value2'")

For just a single parameter,

$sqlParameters = @("variable1 = 'value1'")

By this syntax, assuming printarg.sql contains select $(powerShellVar), this ought work:

$sqlParameters = @("powerShellVar = 'foobar'")
invoke-sqlcmd -ServerInstance computer\instance -inputfile "c:\temp\printarg.sql" -variable $sqlParameters