Microsoft.SqlTools.ServiceLayer.BatchParser.BatchParserException Incorrect syntax was encountered while parsing ''

Question

Hi there,

A PowerShell script that we use to manage a service for quite some time has suddenly erratically stopped working

The error we receive is this:

2023/12/11 00:19:06.668Z|Notice|SQL000| Does-DatabaseExist: Checking if database PG5000NZ exists on SQL-FCI-WFM01
2023/12/11 00:19:14.586Z|Notice|SQL999| Does-DatabaseExist: Incorrect syntax was encountered while parsing ''.
2023/12/11 00:19:14.602Z|Error|AAA000| Process-Database-Restore-Queue: Unexpected error: Incorrect syntax was encountered while parsing ''. occured
2023/12/11 00:19:14.637Z|Warn|EXPS00| When performing activity="Invoke-Sqlcmd" an error="ParserError" occured of errorReason="BatchParserException" at line="415" in powershellScript="\\amznfsx4qwhxyba.ec2.pgol.test\share\Admin\pgol-db-backup-management-service\Utilities.psm1".  The error occured on the scriptText="        $count = Invoke-Sqlcmd -Query "SELECT count(*) FROM master.sys.databases WHERE name = '$DatabaseName'" -ServerInstance $ServerInstance " and is assigned errorEventId="1".
2023/12/11 00:19:14.656Z|Warn|EXPS01| The errorEventId="1" seq="0" is type="Microsoft.SqlTools.ServiceLayer.BatchParser.BatchParserException" with message="Incorrect syntax was encountered while parsing ''.".

This error is being caused by this function, which queries the database to see if a Database exists.

Here you can see the relevant log messages from the above

function Does-DatabaseExist {
    param (
        [Parameter(Mandatory = $true)][String]$DatabaseName,
        [Parameter(Mandatory = $true)][String]$ServerInstance
    )
    try {
        New-LogEntry -level Notice -Event "SQL000" -Message "Does-DatabaseExist: Checking if database $DatabaseName exists on $ServerInstance"
        $count = Invoke-Sqlcmd -Query "SELECT count(*) FROM master.sys.databases WHERE name = '$DatabaseName'" -ServerInstance $ServerInstance
        if ($count.Column1 -eq 1) {
            return $true
        }
        else {
            return $false
        }
    }
    catch {
        New-LogEntry -level Notice -Event "SQL999" -Message "Does-DatabaseExist: $($_)"
        throw $_
    }
}

It's strange that Powershell failed to substitute $DatabaseName into the query string.

It shouldn't be though, as you can see the valid $DatabaseName in the logs (PG5000NZ)

This has only happened recently as well, since when I wrote the script initially a year or two ago, this was working fine

Answer 1

Hi Alex Burling，

Please try this.

$query = "SELECT count(*) FROM master.sys.databases WHERE name = '$DatabaseName'"
$count = Invoke-Sqlcmd -Query $query -ServerInstance $ServerInstance

Or you can use the -Variable parameter.

https://learn.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps

Best Regards,

Ian Xue

---

If the Answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

I would guess this happens because there is a single quote in the input.

Answer 3

Similar to the answer given by @Anonymous but without the need for variable interpolation (and pesky quotation requirements):

$query = "SELECT count(*) FROM master.sys.databases WHERE name = '{0}'" -f $Databasename
$count = Invoke-Sqlcmd -Query $query -ServerInstance $ServerInstance

It is indeed strange that the variable interpolation didn't work.

Is it possible that the $Databasename is altered by the New-Logentry function? You might try adding a line of code after calling that function to verify that $Databasename retained its original value. It wouldn't hurt to check!