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?

Seems like variable quotation problem in the sql file. Add the sql script to the post too. — vonPryz
– vonPryz, Commented Feb 16, 2015 at 9:55
@von - I have. The only line it's got is the print statement — Diskdrive
– Diskdrive, Commented Feb 16, 2015 at 9:58

vonPryz · Accepted Answer · 2015-02-16 10:27:41Z

3

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

answered Feb 16, 2015 at 10:27

vonPryz

24.3k10 gold badges58 silver badges69 bronze badges

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

2 Comments

Diskdrive Over a year ago

Hi, I've pasted exactly as you have stated into a powershell and sql script and I still get a "Invoke-Sqlcmd : Object reference not set to an instance of an object." error. I think it's to do with select $(powerShellVar). Is there a command to check all the existing variables in the context of an sql query cause it looks like that the variable still doesn't exist.

Diskdrive Over a year ago

actually I changed it to this $sqlParameters = "powerShellVar='foobar'" and it worked. thanks!

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related