Reports Fail with “Incorrect syntax near '$'” SQL Error

Issue

Boring reports fail to run, and the logs show the following SQL exception:

[ERR] An exception occurred while iterating over the results of a query 
for context type 'BoringLab.Data.Milestone.MilestoneContext'.
Microsoft.Data.SqlClient.SqlException (0x80131904): 
Incorrect syntax near '$'.

Cause

This issue is usually caused by an unsupported SQL Server database compatibility level.

The Boring database, typically named Boringbase, must have a compatibility level above 120.

In some cases, the Milestone database, typically named Surveillance, may also need to be checked. Both databases should use a compatibility level of at least 130.

This is especially relevant when running Boring with .NET 8, which requires SQL Server compatibility level 130 or higher for some generated queries.

Resolution

1. Open SQL Server Management Studio

Open SQL Server Management Studio and connect to the SQL Server instance that hosts the Boring and Milestone databases.

2. Check the database compatibility levels

Run the following query:

SELECT *
FROM sys.databases;

Review the compatibility_level value for the following databases:

Boringbase
Surveillance

If either database has a compatibility level of 120, it should be updated to 130 or higher.

3. Update the Boring database compatibility level

Run:

ALTER DATABASE Boringbase
SET COMPATIBILITY_LEVEL = 130;

4. Update the Milestone Surveillance database compatibility level, if needed

Run:

ALTER DATABASE Surveillance
SET COMPATIBILITY_LEVEL = 130;

5. Restart Boring services

After updating the compatibility level, restart the Boring services.

6. Verify the fix

The report should now run successfully.

Example

In one case, Boring reports were failing to run. The logs showed the same SQL error related to incorrect syntax near $.

After checking SQL Server Management Studio, the Surveillance database had a compatibility level of 120.

The following command was run:

ALTER DATABASE Surveillance
SET COMPATIBILITY_LEVEL = 130;

After restarting the Boring services, the user logged in to Boring web and successfully ran the report.

Notes

Changing the compatibility level may affect how SQL Server executes queries. If this is a production environment, confirm the change with the customer’s database administrator before applying it.

Articles in this section