Need help getting Invoke-Sqlcmd, -InputFile & -Variable working nicely together

Question

I have File1.ps1 containing the command as follows:

$VariableArray = "PrincipleName='Jack'"
Invoke-Sqlcmd -InputFile "C:\Amit\Test.sql" -Variable $VariableArray -ServerInstance "3STOOGES"

my Test.sql contains
--Test.sql
declare @PrincipleName varchar(20)
SELECT 'PrincipleName=' + @PrincipleName

This gives me output as
Column1
-------

I tried something mentioned here

I tried the options for $(PrincipleName) inside the Test.sql but it complained the SQL server Management Studio.

Please advise as I Have SQL server 2012 with Powershell 3.0 on Windows 2012

You're setting a variable called EmpName but then referencing @PrincipleName in the SQL file. Not sure how that is going to work. Try changing EmpName to PrincipleName. — Keith Hill

CRAFTY DBA CRAFTY DBA · Accepted Answer · 2013-10-10T00:02:35

The syntax is all wrong. SELECT @PrincipleName will just return nothing since variable is not set.

Use this syntax in a powershell ISE.

Import-Module “sqlps” -DisableNameChecking
$MyArray = "MyVar1 = 'String1'", "MyVar2 = 'String2'"
Invoke-Sqlcmd -InputFile "C:\temp\john.sql" -Variable $MyArray

Use this syntax in the sql file.

SELECT $(MyVar1) AS Var1, $(MyVar2) AS Var2;

The output is shown here and works correctly.

enter image description here

Need help getting Invoke-Sqlcmd, -InputFile & -Variable working nicely together

1 Answers