# Request for SQL Server Compatibility Documentation for Holistics

**URL:** <https://community.holistics.io/t/request-for-sql-server-compatibility-documentation-for-holistics/2728>\
**Category:** Report Bugs\
**Created:** [June 30, 2026, 7:42am UTC](https://community.holistics.io/t/request-for-sql-server-compatibility-documentation-for-holistics/2728 "2026-06-30T07:42:27Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Hamza\_Rashed1](https://community.holistics.io/user_avatar/community.holistics.io/hamza_rashed1/32/2171_2.png) [@Hamza\_Rashed1](https://community.holistics.io/u/Hamza_Rashed1)\
**Post date:** [June 30, 2026, 7:42am UTC](https://community.holistics.io/t/request-for-sql-server-compatibility-documentation-for-holistics/2728/1 "2026-06-30T07:42:27Z")

</div>

Hi Team,

I would like to raise a concern that we’ve been facing while developing SQL reports for Holistics.

Most of the SQL scripts I write work perfectly in SQL Server Management Studio (SSMS). However, when I execute the same scripts in Holistics, I often encounter syntax or validation errors that do not exist in the standard SQL Server environment.

So far, I’ve noticed the following differences:

- `ORDER BY` at the end of a query is not accepted by Holistics, even though it is valid SQL Server syntax. This is a significant limitation for us because many of our reports rely on a default sort order, and our clients expect the data to be returned in a consistent order.
- Statements must end with a semicolon (`;`). This requirement is completely fine, and we can easily follow it.

However, even after removing the `ORDER BY` clause and adding semicolons, we are still encountering other SQL behaviors that differ from SQL Server.

In addition, some of the errors returned by Holistics are difficult to interpret because they don’t appear to directly correspond to the SQL being executed. For example, we’ve encountered errors referencing syntax or functions that are not present in our queries, as well as generic transformation errors that provide little indication of the actual cause. This makes troubleshooting significantly more difficult and often requires extensive trial and error.

I’ve already created two support tickets with examples so the team can investigate the specific issues we’re seeing.

In addition, I would like to request the following:

1. **SQL Compatibility Documentation**

2. **Better Error Reporting and Diagnostics**

3. **Support for Essential SQL Features**

Additionally, we’ve encountered several cases where the reported error message does not appear to match the SQL being executed, making troubleshooting extremely difficult.

For example:

- We received the error **“Cannot convert undefined or null to object”** , which was ultimately resolved by adding any comment line at the end of the script like (-- Test) but I’m not sure if this will work always. The SQL itself executes successfully in SQL Server, and the reported error did not clearly indicate the actual source of the problem.
- In another case, Holistics returned **“‘DAYOFWEEK’ is not a recognized datepart option”** , even though our query uses `DATEPART(WEEKDAY, ...)` and does not reference `DAYOFWEEK` anywhere in the SQL.

These types of errors make it difficult to determine whether the issue is related to SQL Server compatibility, SQL parsing, or an internal SQL transformation performed by Holistics. More accurate error messages—or additional debugging information—would significantly reduce development time and make troubleshooting much more efficient.

Having clear documentation, improved diagnostics, and better SQL compatibility would greatly improve the report development experience and reduce the number of support tickets needed for SQL-related issues.

Thank you, and I look forward to your feedback.

---

<div class="post-metadata">

**Author:** ![Hamza\_Rashed1](https://community.holistics.io/user_avatar/community.holistics.io/hamza_rashed1/32/2171_2.png) [@Hamza\_Rashed1](https://community.holistics.io/u/Hamza_Rashed1)\
**Post date:** [July 7, 2026, 2:56pm UTC](https://community.holistics.io/t/request-for-sql-server-compatibility-documentation-for-holistics/2728/2 "2026-07-07T14:56:57Z")

</div>

Please any updates on this?  
I see now the Order By is added to the SQL Server secript and there is no issues when I ran the script. However, the report not updated based on the Order By!!

Looks like the script ignore it or something.

---

<div class="post-metadata">

**Author:** ![HuyVu](https://community.holistics.io/user_avatar/community.holistics.io/huyvu/32/373_2.png) [@HuyVu](https://community.holistics.io/u/HuyVu)\
**Post date:** [July 8, 2026, 4:22am UTC](https://community.holistics.io/t/request-for-sql-server-compatibility-documentation-for-holistics/2728/3 "2026-07-08T04:22:28Z")

</div>

Hi @Hamza_Rashed1

Thanks for the really thorough writeup - this is helpful, and I want to explain what’s going on:

**On the ORDER BY and WITH clause behavior**  
When you run a query in Holistics, we wrap it as a subquery so we can apply our own query logic on top of it such as joining between models and handling limit/sort/pivot on the chart. This is essential to how Holistics works.

SQL Server doesn’t allow an ORDER BY or a top-level WITH/CTE inside a query that’s being nested this way, so the same script that runs fine in SSMS gets rejected once it’s wrapped. That’s the root cause behind those two limitations.

To make this concrete: say your query is

```auto
WITH recent AS (
  SELECT * FROM orders WHERE created_at >= '2024-01-01'
)
SELECT region, SUM(amount) AS total
FROM recent
GROUP BY region
ORDER BY total DESC

```

Our parser automatically converts it into a subquery-friendly form before running, roughly like this:

```auto
SELECT * FROM (
  SELECT region, SUM(amount) AS total
  FROM (
    SELECT * FROM orders WHERE created_at >= '2024-01-01'
  ) as recent
  GROUP BY region
) AS sub

```

Notice the CTE gets inlined as a subquery so it no longer needs a top-level WITH, and the trailing ORDER BY is dropped, so the whole thing runs cleanly once wrapped.  
The nice part is _you don’t have to manually rewrite a WITH CTE into a subquery yourself - which is quite troublesome_ - our processing handles it for you.

For **sorting** , the solution is to set the **sort in the report’s settings rather than in the SQL itself** , so your clients still get a consistent order.

* * *

**DAYOFWEEK bug**

The DAYOFWEEK error is a bug on our side, and I’m sorry for the confusion it caused. Your query is correct - DATEPART(WEEKDAY, …) is valid, and you shouldn’t be seeing a DAYOFWEEK error at all. We’ve fixed the issue in the ticket that you shared

The other error you mentioned looks like a separate issue:

> We received the error “Cannot convert undefined or null to object”, which was ultimately resolved by adding any comment line at the end of the script like (-- Test)

Could you share the query that triggered this one? We’ll dig into it to see if our parser is mishandling something.

* * *

**Documentation**

You’re correct that this needs documentation to explain our pre-processing/parsing of a SQL Server query. I’ll bring this feedback to my team to discuss further and will let you know the progress on this

Best,