CODEWITHSUNDEEP
sql-serverpowershelljenkins-pipeline
arielma
arielma

Reputation: 1398

Powershell sql deploy script is not working with env variavle

I have the below line in constants.groovy file:

myMap = [
    "TEST":["SQL_URL":"Data Source=TEST,123", "credential":"" ]
]

This line in jenkinsfile.groovy:

bat "powershell.exe -file pipeline/powershell/deploy_sql.ps1 ${myMap[env.Environment_Type].SQL_URL}"

And this line in deploy_sql.ps1:

$Env = $args[0]
$msbuild = Start-Process -FilePath "msbuild" -ArgumentList '"Database Services\Database Services.sqlproj"','/t:deploy','/p:Configuration=Release','/p:TargetConnectionString="Data Source="+$Env+";Integrated Security=True;Persist Security Info=False;Pooling=False;MultipleActiveResultSets=False;Connect Timeout=60;Encrypt=False;TrustServerCertificate=False"','/p:BlockOnPossibleDataLoss=True','/p:TargetDatabase="bnhp_fsdb"','-fl','-flp:logfile=msbuild.log' -wait -PassThru

But I get this error in the log:

Deploy error Deploy72002: Unable to connect to target server '+$Env+'. Please verify the connection information such as the server name, login credentials, and firewall rules for the target server.

When I'm running with the full hard coded Data Source, it works, so I guess something is wrong with the powershell syntax

Upvotes: 0

Views: 141

Answers (1)

Mathias R. Jessen
Mathias R. Jessen

Reputation: 174990

PowerShell string literals come in two flavors: verbatim and expandable.

Verbatim string literals are surrounded by single-quotes ('), and expandable strings are surrounded by double-quotes (").

Converting the affected string literal to an expandable string requires us to:

  • Change surrounding quote marks to "
  • Escape all literal "'s inside the string by doubling them
  • Escape any remaining special characters with ` (backtick)

In your case, we would change:

'/p:TargetConnectionString="Data Source="+$Env+";Integrated Security=True;Persist Security Info=False;Pooling=False;MultipleActiveResultSets=False;Connect Timeout=60;Encrypt=False;TrustServerCertificate=False"'

to:

"/p:TargetConnectionString=""Data Source=${Env};Integrated Security=True;Persist Security Info=False;Pooling=False;MultipleActiveResultSets=False;Connect Timeout=60;Encrypt=False;TrustServerCertificate=False"""

Upvotes: 1

Related Questions