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
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
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.
invoke-sqlcmd 'SELECT $(var)' -Variable "var=''" -> no error. You'll have to supply code that fails. — Jeroen Mostert
invoke-sqlcmd 'SELECT $(var)' -Variable "var=" in my case will fail; sorry for beeing unclar — Greg. T.

FoxDeploy FoxDeploy · Accepted Answer · 2017-06-28T14:55:10

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)`""

invoke-sqlcmd empty variable

1 Answers

Edit