invoke-sqlcmd empty variable

Question

I am trying to perform an SQL query using Invoke-SqlCmd launching script as inputfile which contains SQLCMD variables, as $(varPROJECTNAME).

For example: CREATE DATABASE [$(varPROJECTNAME)$(varDBNAME)] In such a case, i want to be able to set an empty string as varPROJECTNAME value.

This request will run successfully with classical SQLCMD tool, but with Invoke-SqlCmd, i get error, script variable varPROJECTNAME is undefined.

Invoke-Sqlcmd  -Username $LoginSQL -Password $PasswordSQL -ServerInstance $InstanceSQL -Verbose  -InputFile "$rootPackage\DB\UpdateDB\00-initSql\00-SubsTechCreateDatabase.sql"  -Variable "varPROJECTNAME=$projectName","varDBNAME=$DatabaseName"

In case above, if $projectName="", There will be an error

Is it possible to set a default value calling Invoke-SqlCmd, or from sql script point of view, assign a default value to the variable when it's undefined?

Many thanks in advance for your help

We might wanna need more description and code as well,the question is very unclear — Chetan Kulkarni
– Chetan Kulkarni, Commented Jun 28, 2017 at 13:36
sqlcmd.exe doesn't like it if you do that either: sqlcmd -Q "SELECT '$(var)'" -> "'var' scripting variable not defined." It will then proceed anyway, leaving the variable without any substitution, literally as $(var) (unless you pass -b, then the batch is aborted and nothing happens, in the same manner as Invoke-SqlCmd). Relying on this is ill-advised. If you must do this, I recommend identifying the variables and explicitly passing empty values anyway. — Jeroen Mostert
– Jeroen Mostert, Commented Jun 28, 2017 at 13:40
That is the problem in fact, passing empty variables with SQLCMD will work; in your case, if var=='' it is an empty string that will be used in executed query. With Invoke-SqlCmd, the query will not run at all, despite i explicitly set variable to '' — Greg. T.
– Greg. T., Commented Jun 28, 2017 at 13:46
invoke-sqlcmd 'SELECT $(var)' -Variable "var=''" -> no error. You'll have to supply code that fails. — Jeroen Mostert
– Jeroen Mostert, Commented Jun 28, 2017 at 13:52
invoke-sqlcmd 'SELECT $(var)' -Variable "var=" in my case will fail; sorry for beeing unclar — Greg. T.
– Greg. T., Commented Jun 28, 2017 at 13:54

FoxDeploy · Accepted Answer · 2017-06-29 15:17:56Z

0

In your case, the Invoke-SQLCommand tool is very helpfully running some error checking, ensuring that all of the variables which you've defined here are passed along to SQL to setup your database just so.

I would propose a change, let's add some logic to your script to see if you've specified a $ProjectName value, and if so, pass that value along. If not, run a slightly different command instead.

If($projectName -ne $null){
 $variables = "varPROJECTNAME=$projectName","varDBNAME=$DatabaseName"
   }
 else{
 $variables = "varDBNAME=$DatabaseName"
  }

Invoke-Sqlcmd  -Username $LoginSQL -Password $PasswordSQL -ServerInstance $InstanceSQL `
  -Verbose  -InputFile "$rootPackage\DB\UpdateDB\00-initSql\00-SubsTechCreateDatabase.sql" `
  -Variable $variables

Edit

Could you try this example instead?

In your current code, replace this

-Variable "varPROJECTNAME=$projectName"

with

-Variable "varPROJECTNAME=`"$($projectName)`""

edited Jun 29, 2017 at 15:17

answered Jun 28, 2017 at 14:55

FoxDeploy

13.8k3 gold badges42 silver badges58 bronze badges

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

5 Comments

Jeroen Mostert Over a year ago

That's not going to work, because the whole command fails if you leave a variable unspecified. As long as the .sql contains $(varPROJECTNAME), it must be passed explicitly.

FoxDeploy Over a year ago

In this example, we only pass variables with values. Notice that line 2 passes both varProjectName and varDBName, while line 5 only provides a value for varDBName. This should solve your issue.

Greg. T. Over a year ago

well no, Jeroen is right, your proposal will solve only your powershell issue, but the script will not run as varPROJECTNAME is not set on SQL side

FoxDeploy Over a year ago

Oh, so you're saying that your SQL Script .SQL File might have a blank value for varPROJECTNAME? Can you try the second example?

Greg. T. Over a year ago

@FoxDeploy, thanks for your suggestion, i tried it but unsuccessfully

hector-j-rivas · Accepted Answer · 2022-05-04 17:44:38Z

Coalescing to '""' in PowerShell and then checking for that in SQL worked for me.

PowerShell:

# coalesce command if undefined, whitespace or empty
if ("$($step.command)".Trim().Length -eq 0) {
    $command = '""'
} else {
    $command = $($step.command)
}

$variables = @(
    "job_name=$($job.name)",
    "step_name=$($step.name)",
    "step_id=$($step.id)",
    "package_path=$($step.package)",
    "command=$command",
    ...
)

Invoke-Sqlcmd -InputFile "$PSScriptRoot\upsert_job_step.sql" -Variable $variables -ConnectionString $connString -Verbose -OutputSqlErrors $true

SQL:

DECLARE @command varchar(2047) = CASE
  WHEN NULLIF(TRIM('$(command)'), '""') IS NOT NULL
  THEN '$(command)'
  ELSE  FORMATMESSAGE('/ISSERVER "\"\SSISDB\$(package_path)\"" /ENVREFERENCE %i ..., @referenceId)
END;

The value can be anything (say undefined or null) it just needs to be the same in both scripts.

Collectives™ on Stack Overflow

invoke-sqlcmd empty variable

2 Answers 2

Edit

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Edit

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related