Running reports fails and logs show Database Error: “Incorrect syntax near ‘$’”

Symptoms

When creating or running a report, the operation fails and the following error appears in the logs:

[ERR] An exception occurred while iterating over the results of a query for context type
'BoringLab.Data.Milestone.MilestoneContext'.

Microsoft.Data.SqlClient.SqlException:
Incorrect syntax near '$'.

Cause

This error typically occurs when the SQL Server database compatibility level is set too low.
Some queries used by Boring require a newer compatibility level in order to run successfully.

Resolution

Step 1: Update the Boring Database Compatibility Level

1. Open SQL Server Management Studio (SSMS).

2. Connect to the SQL Server hosting the Boring database.

3. Click New Query.

4. Paste the following SQL script:

ALTER DATABASE BoringBase
SET COMPATIBILITY_LEVEL = 130

5. Click Execute.

Why this matters
Compatibility level 130 (SQL Server 2016) is required for proper execution of certain Boring queries.

Step 2: Restart Boring Services

After the script completes successfully:

1. Open IIS Manager

2. Recycle the application pool:

   • BoringLabService AppPool

3. Open Windows Services

4. Restart:

   • Boring Service Host

Step 3: Verify

• Log back into Boring Toolbox

• Attempt to create or run the report again

The error should no longer occur.

Additional Notes

• This change is safe and does not modify or delete any data.

• The correct database name is BoringBase (default)
  If you're using a custom Boring database name, update the query above to match that name.

• The SQL Server version supports compatibility level 130. Supported in SQL 2016 and newer versions.

• If the issue persists after updating the Boring database, repeat the same steps for the Milestone database:

  • Database name: Surveillance

  • Use the same compatibility level:

    ALTER DATABASE Surveillance
    SET COMPATIBILITY_LEVEL = 130
    

• Ensure the Boring service account has db_owner permissions on both databases if needed