SQL generated for legacy ODBC driver

[Aalbregt]

Hello,

We are using a legacy ERP system, that runs on a proprietary database engine.

An ODBC driver specifically for this database engine is available.

The ODBC connection is set up and we use Power BI to connect to that DSN.

After selecting the ODBC option we select the DSN and hit OK.

At the Navigoter screeen we see the database tables.

But when we select a table we receive the following error:

DataSource.Error: ODBC: ERROR [HY000] SQLPrepare: Drive not capable -- ORDER BY is not supported

Fair enough, we use a legacy database and a proprietary ODBC driver, so we should not use ORDER BY in the query.

But we are only at the stage to show a preview of the data.

Is it a setting in Power BI by which we can control if an 'order by' is used in the query to retrieve the data preview?

And also at the Query Editor.

Thanks,

Erik

[v-qiuyu-msft]

Hi @Aalbregt,

Does the issue happen to all tables or specific report? 

When you select the ODBC DSN, please write a query to return the table data to see if the same issue occurs? 

Best Regards,
Qiuyun Yu 

[Aalbregt]

Hi Qiuyun, Thanks for the update. It happens for all tables. I captured the sql statement executed on another DBMS during the datapreview and there I also saw the 'order by' in the query. At that DBMS that works...that's an SQL Server 2016. I used the query as SQL Statement when configuring the ODBC datasource in Power BI. The same error occurred when I used the original query including the order by. The query did work when I removed the order by-part. It's possible to work by writing all SQL statements myself, but then I can't navigate the database and the available tables by using Power BI. So their is a downside. Maybe you have some other tips? Thanks in advance, Erik