KB489721: SQL Server Syntax Error Near '(' When Using STRING_AGG in MicroStrategy

Jinlei Cai

Quality Engineer, Senior • MicroStrategy

MicroStrategy reports using STRING_AGG against SQL Server may fail with "Incorrect syntax near '('".

Symptom

MicroStrategy SQL fails with error when using STRING_AGG in T-SQL.

[MicroStrategy][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server] Incorrect syntax near '('.

Same error occurs over JDBC:

[MicroStrategy][SQLServer JDBC Driver][SQLServer] Incorrect syntax near '('.

Example SQL (excerpt):

select
  STRING_AGG(
    COALESCE(
      CASE WHEN LEN(TRIM(CAST([pa11].[WJXBFS1] AS VARCHAR))) > 0
           THEN FORMAT(TRY_CAST([pa11].[WJXBFS1] AS FLOAT), 'G') END,
      CAST([pa11].[WJXBFS1] AS VARCHAR)
  ), ', ')
  WITHIN GROUP (ORDER BY [pa11].[WJXBFS1] asc) [WJXBFS1]
from ##ZZTLOOESFRWMD000 [pa11];

Cause

STRING_AGG, TRIM, TRY_CAST, FORMAT, and the WITHIN GROUP ordering syntax are only supported at newer SQL Server database compatibility levels. When a database is set to an older compatibility level (for example, 100 for SQL Server 2008), the SQL parser does not recognize these functions/syntax and raises “Incorrect syntax near '('.

First confirm the SQL Server instance version. If the instance is lower than SQL Server 2017, update or select a data source that targets the correct (older) database version. Then check the database compatibility level:

SELECT
  @@VERSION AS version,
  compatibility_level,
  name
FROM sys.databases
WHERE name = DB_NAME();

If compatibility_level is older (e.g., 100, 110, 120), modern T‑SQL such as STRING_AGG will fail even on a newer SQL Server instance.

Solution

Raise the database compatibility level to one that supports STRING_AGG and related functions. STRING_AGG requires at least compatibility level 140 (SQL Server 2017).

Confirm the SQL Server instance version:SELECT @@VERSION AS version;
If the instance version is lower than SQL Server 2017, select the correct (older) SQL Server version in your MicroStrategy data source/driver configuration and avoid using STRING_AGG on this instance.
If the instance version is SQL Server 2017 or later, check the database compatibility level:SELECT compatibility_level, name FROM sys.databases WHERE name = DB_NAME();
If compatibility_level is below 140, raise it (replace <database> with your DB name):

ALTER DATABASE <database> SET COMPATIBILITY_LEVEL = 140; -- SQL Server 2017 features incl. STRING_AGG

4. Reconnect the MicroStrategy DSN/session and rerun the report.

Reference

Microsoft Document: ALTER DATABASE (Transact-SQL) compatibility level

Comment

0 comments

Details

Knowledge Article

Published:

May 12, 2026

Last Updated:

May 12, 2026

Symptom

MicroStrategy SQL fails with error when using STRING_AGG in T-SQL.

[MicroStrategy][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server] Incorrect syntax near '('.

Same error occurs over JDBC:

[MicroStrategy][SQLServer JDBC Driver][SQLServer] Incorrect syntax near '('.

Example SQL (excerpt):

select
STRING_AGG(
COALESCE(
CASE WHEN LEN(TRIM(CAST([pa11].[WJXBFS1] AS VARCHAR))) > 0
THEN FORMAT(TRY_CAST([pa11].[WJXBFS1] AS FLOAT), 'G') END,
CAST([pa11].[WJXBFS1] AS VARCHAR)
), ', ')
WITHIN GROUP (ORDER BY [pa11].[WJXBFS1] asc) [WJXBFS1]
from ##ZZTLOOESFRWMD000 [pa11];

Cause

SELECT
@@VERSION AS version,
compatibility_level,
name
FROM sys.databases
WHERE name = DB_NAME();

If compatibility_level is older (e.g., 100, 110, 120), modern T‑SQL such as STRING_AGG will fail even on a newer SQL Server instance.

Solution

Raise the database compatibility level to one that supports STRING_AGG and related functions. STRING_AGG requires at least compatibility level 140 (SQL Server 2017).

Confirm the SQL Server instance version:SELECT @@VERSION AS version;

If the instance version is lower than SQL Server 2017, select the correct (older) SQL Server version in your MicroStrategy data source/driver configuration and avoid using STRING_AGG on this instance.

If the instance version is SQL Server 2017 or later, check the database compatibility level:SELECT compatibility_level, name FROM sys.databases WHERE name = DB_NAME();

If compatibility_level is below 140, raise it (replace <database> with your DB name):

ALTER DATABASE <database>
SET COMPATIBILITY_LEVEL = 140; -- SQL Server 2017 features incl. STRING_AGG

4. Reconnect the MicroStrategy DSN/session and rerun the report.