Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hpayne93
Frequent Visitor

The incoming request has too many parameters

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

9 REPLIES 9
Vee
Regular Visitor

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?

v-rongtiep-msft
Community Support
Community Support

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
Frequent Visitor

@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. 

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

dpronta
Frequent Visitor

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: 

 

dpronta_0-1685149688966.png

 

vk_pbi
Resolver II
Resolver II

Hi @hpayne93 

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

RJoan
New Member

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
Not applicable

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?

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.