The incoming request has too many parameters

[hpayne93]

Hi,

I'm a fairly new user to power BI!

I'm connecting to Dynamics 365 (Dataverse) in power BI desktop. I'm using a mixture of direct query and import. . A few visuals have stopped working and show the error below. Would appreciate some guidance how to correct the issue!

OLE DB [datasource.error] Microsoft SQL:  2the incoming request has too many parameters....."

Thanks

[AndersSolhoej]

Any one got news on this?
Still have a customer where a simialar issue suddenly started during the last couple of weeks and nothing changed to explain this.

Only direct query mode tables and data amount shouldn't really be the issue.

[flopicara]

I’m experiencing the same issue with a Power BI report connected via DirectQuery to Dataverse. Does anyone have a better solution? I’d prefer not to split the report into two.

"datasource.error: microsoft sql: "too many literal values were passed in the query. the maximum number is 2100 line:47, position:22940"

[TDargan]

I started receiving this error yesterday too. Did you find a solution?

Thanks,

Thomas 

[dfmika08]

Mine just started couple days too, any solution?

[AndersSolhoej]

Hi
Did any of you guys find any other info on this? We have a solution suddenly seeing the same issue, been working until last week and now faces the error  "datasource.error: microsoft sql: "too many literal values were passed in the query. the maximum number is 2100 line:47, position:22940" and nothing changed in the report. Did something change on the MS Dynamics CRM side maybe? @Microsoft 

[AndersSolhoej]

Hi
Did any of you guys find any other info on this? We have a solution suddenly seeing the same issue, been working until last week and now faces the error  "datasource.error: microsoft sql: "too many literal values were passed in the query. the maximum number is 2100 line:47, position:22940" and nothing changed in the report. Did something change on the MS Dynamics CRM side maybe? @Microsoft 

[Anonymous]

I have the same issue with a mix of direct and import queries, but it only happens for a specific user.

Initially, no data was loading at all.

I checked his permissions on the Workspace and updated them.

In Dataverse, permissions are managed through the role, and that is set up as it should.

After these changes, he can see some data but not all.

All the custom tables access is set the same for all tables.

The is no role-based access in Power BI.

Any suggestion?

[flopicara]

Hi Vee! Were you able to solve this issue? I'm still looking for a solution.

[Anonymous]

Hi @hpayne93 ,

The error message you are seeing is related to the number of parameters being passed to the SQL server. This error can occur when using Direct Query and Import in Power BI Desktop. One possible cause of this error is a network connectivity issue between the gateway machine and the SQL data source. You can perform a UDL test to check for network connectivity issues. Another possible cause is that the SQL tables need specific configuration. You can check if there are any duplicate or null values in the column that is causing the issue and delete the duplicate record or replace the null values with any character.

How to Get Your Question Answered Quickly 

If it does not help, please provide more details.

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dpronta]

@hpayne93 

I think i may have come up with the solution to avoid this issue though its a tad finicky depending on what your data model and measures look like.

So i would first analyze your data model to understand which of the relationships is causing the WHERE clause explosion i described previously. You can use something like DAX studio to understand the SQL thats being generated through your direct query. 

Now for the measure, instead of using the relationship for the visuals that are causing this issue to occur, have the relationship be inactive by default in your data model. Then, i nthe measure and relationship that is driving this issue, modify your DAX in the following way to mimic the relationship existing. 

CALCULATE([expression], direct_query_column_name in VALUES(import_column_name))

This will apply the filter context transition that essentially acts as though a relationship exists between the 2 tables even though it doesnt. For whatever reason this bypasses the SQL issue that i noticed and allows for the calculation to occur properly. 

EDIT: This solution for some reason only works sometimes.................... one of my reports in a different environment just got the same error all of a sudden even with this alternative way of doing it so. Seems to not be particularly stable unfortunately. 

[Anonymous]

This solution seems to work for me! Thank you alot 👌

[dpronta]

I am having the same issue and i think i know what its happening.

This specifically occurs in mixed models when a filter is being applied to a visual that has a measure with a component that is direct query. When the filter is applied, in some cases (<20,000 records it seems) the engine puts all of those values into the direct query instead of processing within the formula engine this causes these massive WHERE clauses to be generated on the direct query and if there is more than 2100 values in the where clause, it throws this error. 

Whats weird is that if the filter has enough entities it seems the engine decides not to push the values into the where clause. I have no idea how to fix this but its super frusturating and have been trying to find work arounds for the past 3-4 weeks. 

An example of the massive WHERE clause can be seen here: 

[vk_pbi]

Hi @hpayne93 

I would recommend to use Performance anlayser to see for any unnecessary calls. You can use DAX studio too.

[Anonymous]

I am currently running into the same problem. Last week the same visuals in my report still worked, now I get an error message that too many parameters are included in the request to the data source. I wonder if anyone has a solution to this problem.

It is regarding a dataverse datasource. 

OLE DB or ODBC error: [DataSource.Error] Microsoft SQL: "The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request."

[Anonymous]

I have the same issue, also just popped up in the last week. Splitting the report in to two and/or killing the relationship is not a viable solution. Is there really not anyone on the MS side who can provide an actual explanation of the error and a way to solve it without killing the relationship?

[hpayne93]

The only thing that worked for me was splitting my report into two separate ones. It's not ideal but saved a headache!