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....."
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 👌
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:
I would recommend to use Performance anlayser to see for any unnecessary calls. You can use DAX studio too.
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."
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!
Find out more about the May 2023 update.
Share your Data Story with the Community in the Data Stories Gallery.