0

I've been provided a .sql script which will create a database. Within the script there is a variable which is used for the Database name.

:setvar DatabaseName database-name-1

Im calling the script from a pipeline which calls a PowerShell script which uses the Invoke-SQLCmd command in the following format

Invoke-Sqlcmd -ServerInstance $ServerInstance -AccessToken $access_token -InputFile $DbInputFile -Variable $dbName

I've created the variable in the PowerShell as follows

$dbName = @("DBNAME=$db_name")

$db_name is just a string that contains the name of the DB I want to create.

Within the sql script I've tried using various combinations to use the $db_name as the database name

:setvar DatabaseName "`$(DBNAME)"
:setvar DatabaseName "$(DBNAME)"
:setvar DatabaseName $DBNAME

but whenever I run the pipeline is just uses the value specified, it doesn't expand the variable, so it would create a database name as $DBNAME or `$(DBNAME)

Im not sure what im doing wrong here, or if what I am trying to do is possible.

What I am expecting to see is the database created with the actual name defined within the $db_name variable.

jarlh
  • 42,561
  • 8
  • 45
  • 63
Syntegrity
  • 11
  • 2
  • 1
    Variables don't expand within variables. This restriction is not specific to `Invoke-Sqlcmd`, `sqlcmd.exe` behaves this way too. Because `:setvar` has the highest priority, you cannot use it to provide "defaults" that the external script should override. Leave out the `:setvar` directive and pass the variable directly as `"DatabaseName=$db_name"`. Of course this means the script will fail in a non-obvious way if this value is not supplied, but, well, that's `sqlcmd` for you. I still have no idea why someone thought it was a worthwhile idea to replicate it in PowerShell, warts and all. – Jeroen Mostert Feb 20 '23 at 22:33
  • Thank you - for the explanation - that sorted the issue! – Syntegrity Feb 21 '23 at 08:51
  • I can't see how to mark that as an answer though – Syntegrity Feb 21 '23 at 08:51

0 Answers0