Invoke-sqlcmd: -Variable @("DB_EMC_PREF=AML","DB_AML_PREF=GBSET") works but -Variable $StrNameVal doesn't (Parameters from XML)

Question

On the MS website it says that the string variable for parameter in invoke-sqlcmd needs to be in Var-'Val' format but it doesn't work for me. (https://learn.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps)

When I use $StrNameVal with -Inputfile switch (using an SQL file) it fails. When I use "-Variable @("DB_EMC_PREF=AML","DB_AML_PREF=GBSET")" it works It works with the -Query switch.

I need to pick the parameters from Variable.xml & it can change by any one at anytime. Using the following code. (Refer the 2 different ways I am making the array, none of them work)

I'm specially interested to understand why the last (4th) invoke works if the 3rd invoke doesn't? :|

How should I use the XML to create my parameters variable?


[xml]$XmlDocument = Get-Content -Path $VarXMLPath
cls
[String[]]$StrNameVal = @()
foreach ($Variable in $XmlDocument.environmentSet.environment.variable)
{
$StrNameVal+=$Variable.name + "='" + $Variable.value + "'"
}
$StrNameVal

[String[]]$StrNameVal1 = @()
foreach ($Variable in $XmlDocument.environmentSet.environment.variable)
{
$StrNameVal1+='"' + $Variable.name + '=' + $Variable.value + '"'
}
$StrNameVal1

Start-Transcript -path $LogsFullPath -append


$files=get-childitem $ExecutionPath -Filter *.sql | Where {$_.Name -ne'0000Logs'}
foreach ($f in $files){
    (Invoke-Sqlcmd -AbortOnError -InputFile $f.FullName -ServerInstance WPU8L0083570 -Variable $StrNameVal -Verbose) 2>&1 | out-host 
    (Invoke-Sqlcmd -AbortOnError -InputFile $f.FullName -ServerInstance WPU8L0083570 -Variable @("DB_EMC_PREF='AML'","DB_AML_PREF='GBSET'") -Verbose) 2>&1 | out-host
    (Invoke-Sqlcmd -AbortOnError -InputFile $f.FullName -ServerInstance WPU8L0083570 -Variable $StrNameVal1 -Verbose) 2>&1 | out-host 
    (Invoke-Sqlcmd -AbortOnError -InputFile $f.FullName -ServerInstance WPU8L0083570 -Variable @("DB_EMC_PREF=AML","DB_AML_PREF=GBSET") -Verbose) 2>&1 | out-host

}
Write-Host "Executions completed"
Stop-Transcript

Output:

score 0 · Answer 1 · answered May 18 '22 at 08:49

While playing wit the XML I realized that the 3rd invoke does work. The issue is that if even one variable has a blank value like this it breaks:

<variable name="Any_Variable" value="" />

Any good ideas to pass the value if it is blank to he parameter without breaking?

Invoke-sqlcmd: -Variable @("DB_EMC_PREF=AML","DB_AML_PREF=GBSET") works but -Variable $StrNameVal doesn't (Parameters from XML)

1 Answers1