Unanswered

Too many literal values were passed in the query. The maximum number is 2100

(0) Share

Report

Posted on by DS-02121352-0

Hello, we're working on a Power BI solution that involves a composite model with Vertipaq tables and DirectQuery Dataverse tables. This entails Power BI querying Dataverse directly but first injecting those queries with literals based on the relationships in the composite model (or on other aspects of the DAX being used).

However, we've realized that there seems to be a limit on the size of the queries which Dataverse will accept. We get this error when using the standard Dataverse (CDS) connector:

"OLE DB or ODBC error: [DataSource.Error] Microsoft SQL: {"Message":"Too many literal values were passed in the query. The maximum number is 2100}"

Similarly, if we connect to the TDS endpoint using the SQL Server connector, we can produce this error when running a query:

"One or more errors occurred. {"Message":"Too many literal binary expressions in the query. The maximum number is 200"} The exception was raised by the IDbCommand interface."

This is a pretty significant limitation from a reporting POV. Injecting literals is the standard Power BI behavior for composite models. 2100 values is not much to work with, especially if you have dimension tables that are larger than that.

Questions:

Is it possible to raise this limit of 2100 literals in queries that Dataverse will accept?
Is there a way to circumvent the limit?
Where is this limitation documented? We have not been able to find the location.

Many thanks

(CC @MScottSewell)

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (2)

Answers (0)

bscarlavai33 738 Super User 2025 Season 2 on at

Like (1)

Report
Too many literal values were passed in the query. The maximum number is 2100

It doesn't appear to be a Dataverse limitation. From what I'm reading, it seems like a SQL / .NET limitation - https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters#passing-multiple-rows-in-previous-versions-of-sql-server

Does this thread help at all? https://community.fabric.microsoft.com/t5/Power-Query/Too-many-literal-binary-expressions-in-the-query-The-maximum/td-p/3468550

"
We did some debugging and found that the issue is that we had a visual with columnns from both a direct query table and an import table.

To further explain, the issue is that Power BI tries to generate a query for the server to load but it due to the joins it has to do (compounded by the amount of columns and rows returned) it will get to a point where the server can't/won't load it.

To see this, you can use the query diagnostics tool in Power BI desktop to see what the query to the server looks like (https://learn.microsoft.com/en-us/power-query/query-diagnostics).

The only options to fix this (at least from our perspective):

Add filters for the table and prolong the issue

Change the direct query table(s) to import"

Was this reply helpful? Yes No
DS-02121352-0 2 on at

Like (0)

Report
Too many literal values were passed in the query. The maximum number is 2100

@bscarlavai33, you make an interesting point. Thank you for the reference.

However, what does not makes sense is that:

This behavior occurs with both the CDS connector and the regular SQL Server connector, potentially indicating that any differences in protocol are not the source of the problem.

This behavior does NOT occur with our Azure SQL servers. When setting up a Power BI report with similar conditions as above, Azure SQL Server will accept many more literals than 2100. In fact, we don't know what the limit is; we haven't reached it.

Combined, these things make me question whether it is truly related to a SQL limitation. Thoughts?

Was this reply helpful? Yes No