Data Gateway update: Causes SQL Server error: An expression services limit has been reached

[cymorg]

After updating my on-premesis data gateway to v3000.63.6 (October 2020 (Release 2)) one of my scheduled report refreshes (connecting to SQL Server 2012) is failing with a SQL Server error "An expression services limit has been reached".  That error typically indicates a SQL query is too complex.  The report refresh had been working without a problem for 18 months and immediately after applying the Oct. 2020 gateway update the error started to appear.  My previous gateway version was quite old (June 2019).

How can this be rectified?  The only workaround I can think of is to write my views (used by the report dataset) into temporary tables on SQL Server and use the tables instead of views in the dataset.  I can do that but I shouldn't have to - it's just more work with no value added.  Ideally updating the gateway would be a painless operation.

[cymorg]

More info. on this case here... https://community.powerbi.com/t5/Service/Data-Gateway-update-Causes-SQL-Server-error-An-expression/m...

[v-lili6-msft]

hi  @cymorg 

it work fine for me. and i search for the whole forum, but don't find any similar posts, you may try to use an old version gateway to have a try.

https://docs.microsoft.com/en-us/data-integration/gateway/service-gateway-monthly-updates

and if you still have the problem, you may create a support ticket for assistance.

Regards,

Lin

[cymorg]

I had about 40 transformations applied to the data in PowerBI Desktop, mostly Replace() methods.  Reduce this to under 32 eliminates the problem.  So, whatever way Microsoft send the DAX to SQL Server seems to have changed between gateway versions.  I ended up moving all my Replace() methods out of PowerBI and into SQL Server and the problem is resolved.