CODEWITHSUNDEEP
sql-serverpowershell
mcmattu
mcmattu

Reputation: 101

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

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

    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

Upvotes: 1

Views: 6209

Answers (1)

CRAFTY DBA
CRAFTY DBA

Reputation: 14915

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

Upvotes: 2

Related Questions