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, Jun 28 '17 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, Jun 28 '17 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., Jun 28 '17 at 13:46
`invoke-sqlcmd 'SELECT $(var)' -Variable "var=''"` -> no error. You'll have to supply code that fails. — Jeroen Mostert, Jun 28 '17 at 13:52
invoke-sqlcmd 'SELECT $(var)' -Variable "var=" in my case will fail; sorry for beeing unclar — Greg. T., Jun 28 '17 at 13:54
Hey man, your question is getting downvoted because you haven't provided a good example of your current code, and your description of what you want to accomplish is kind of confusing. Can you edit your question to include a code sample that you're working on, and we'll try to help you out? — FoxDeploy, Jun 28 '17 at 14:04
OK, I see the problem. `sqlcmd -Q "SELECT '$(var)'" -v var=""` works and selects the empty string, `invoke-sqlcmd 'SELECT ''$(var)''' -Variable "var="` will not work because you can't pass the empty string (at least not that way). (Yes, having this in the question helps, comments are fleeting.) — Jeroen Mostert, Jun 28 '17 at 14:04
@FoxDeploy il will edit my post, but you can look at JereoMostert comment — Greg. T., Jun 28 '17 at 14:07
Can you write temporary files? If so, abandoning the primitive variable substitution of SQLCMD and doing it in PowerShell is an option. It's clumsy, but a `(Get-Content $inputfile).Replace('$(varPROJECTNAME)', "$projectname") | Out-File -Enc Utf8 tempscript.sql` would make a new script with the variable pre-substituted. Doing this dynamically for all variables is a bit more code. — Jeroen Mostert, Jun 28 '17 at 15:23
Silly me, there's a `-Query` parameter you could pass the contents of the script to directly, of course, so you'd have no need of temporary files (they could still be good to see substitutions and/or speed things up). — Jeroen Mostert, Jun 28 '17 at 15:32
hmm, yous suggestion sounds good @JeroenMostert ! :-) I'll try it right now — Greg. T., Jun 28 '17 at 15:36
Great! @JeroenMostert, your comment is ok for me; i can accept it as an Answer if you post it as it. However, from a Invoke_SqlCmd point of view, and if there is no other sollution, I consider that this is a limitation — Greg. T., Jun 28 '17 at 15:44
It is a limitation, but not the only one; if you look at [this question](https://stackoverflow.com/questions/35157090/), someone else wrestled with the fact that `=` couldn't be used in a variable at all. (The alternative script mentioned there might be something for you as well, but I haven't checked.) `Invoke-SqlCmd` is a bit dodgy, considering doozies like [executing failed scripts twice](https://connect.microsoft.com/SQLServer/Feedback/Details/811560) (that has since been fixed, fortunately). At present I lack the time to write up a decent answer, feel free to post one yourself. — Jeroen Mostert, Jun 28 '17 at 15:51

FoxDeploy · Answer 1 · 2017-06-29T15:17:56.980

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 '17 at 15:17

answered Jun 28 '17 at 14:55

FoxDeploy

12,569
2
33
48

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. – Jeroen Mostert Jun 28 '17 at 14:58
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. – FoxDeploy Jun 28 '17 at 15:07
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 – Greg. T. Jun 28 '17 at 15:12
Oh, so you're saying that your SQL Script .`SQL` File might have a blank value for `varPROJECTNAME`? Can you try the second example? – FoxDeploy Jun 29 '17 at 15:16
@FoxDeploy, thanks for your suggestion, i tried it but unsuccessfully – Greg. T. Jun 30 '17 at 13:39

hector-j-rivas · Answer 2 · 2022-05-04T17:44:38.193

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.

invoke-sqlcmd empty variable

2 Answers2

Edit