1

I've used this type of coding in many PS scripts:

$SQlScriptParameters = (
    "FtpServer='$FtpServer'", 
    "RotkatalogFörFTP='$RootFolderOnFtpServer'",
    "HostUrlForWebAuthorization='$HostUrlForWebAuthorization'",
    "UrlForSsekListener='$UrlForSsekListener'",
    "RotkatalogFörAvstämningar='$RootFolderForReconciliations'",
    "UrlFörWebbtjänsten='$UrlForWebSite'"
)
$result = Invoke-Sqlcmd -ServerInstance '(local)' -Database "$DatabaseName" -InputFile "$SqlAdaptSystemParameters" -QueryTimeout $maxQueryTime -AbortOnError -Variable $SQlScriptParameters

It worked fine until one of the variables contained a colon in its value (like $HostUrlForWebAuthorization). Now I get the following parsing error:

Invoke-Sqlcmd : 
At line:1 char:1
+ Invoke-Sqlcmd -ServerInstance '(local)' -Database "$DatabaseName" -In ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ParserError: (:) [Invoke-Sqlcmd], ParserException
    + FullyQualifiedErrorId : ExecutionFailureException,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

The string value in the $SQlScriptParameters string array is as follows when displayed in the Windows PowerShell ISE debugger:

HostUrlForWebAuthorization='http://ensymappacc1.cypointmo.local:80/'

This is how it looks like in the SQL input file

SET NOCOUNT ON

DECLARE @FtpServer nvarchar(500)
DECLARE @RotkatalogFörFTP nvarchar(500)
DECLARE @HostUrlForWebAuthorization nvarchar(500)
DECLARE @UrlFörSsekLyssnaren nvarchar(500)
DECLARE @RotkatalogFörAvstämningar nvarchar(500)
DECLARE @UrlFörWebbtjänsten nvarchar(500)

-- Format: uname:pwd@hostname:port
SET @FtpServer = $(FtpServer)

SET @RotkatalogFörFTP = $(RootFolderForFTP)

-- Format: http://hostname:port/
SET @HostUrlForWebAuthorization = $(HostUrlForWebAuthorization)

SET @UrlFörSsekLyssnaren = $(UrlFörSsekLyssnaren)

SET @RotkatalogFörAvstämningar = $(RotkatalogFörAvstämningar)

-- Format:  https://hostname:port/
SET @UrlFörWebbtjänsten = $(UrlFörWebbtjänsten)

How to set the $SQlScriptParameters so I don't get a parse error on colon?

matsovef
  • 11
  • 1
  • Figured it out. It turns out that the parse error is on the SQL-file. Using non-English characters as variable name for the passed arguments (as `$(RotkatalogFörAvstämningar)` causes the parse error. When only using characters in the English alphabet it works as expected. – matsovef Aug 09 '18 at 10:36

0 Answers0