Pass parameters to SQL Script through Invoke-SQLCmd

786 Views Asked by At

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.

0

There are 0 best solutions below