Erro do OLE DB ou do ODBC : [DataSource.Error] Microsoft SQL

[Anonymous]

Hi All , I'm facing a data refresh error after update my PBI Desktop, now its showing the erros below in two tables that I have (in the old version this issue doesnt occu.  When I edit the queries the errors are not showed and it seems to be some record in the database that can be causing the error.

Does anybody know how can I debug this or know how to fix it?

"Erro do OLE DB ou do ODBC : Exceção de HRESULT: 0x80040E4E"

"Erro do OLE DB ou do ODBC : [DataSource.Error] Microsoft SQL: Incorrect syntax near the keyword 'union'"

thanks a lot.

[mwegener]

Hi @Anonymous ,

do you use pure Power Query or SQL queries?

UNION indicates that a query is being appended. Do you have a corresponding query?

You can use tools such as the SQL Profiler to trace the query on the database.

[Anonymous]

Hi @mwegener , I've noticed that the querie that is showing problem is a combined querie, it always show error when it reaches line 4096 in the update process.  During the process, when it reachs linhe 4096 it restart the counting and the error is showed.  Any tips?

[v-lionel-msft]

Hi @Anonymous ,

This seems to be an error in the SQL query in the database. You can check the data source in the database.

Best regards,
Lionel Chen

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

[Anonymous]

Hi @v-lionel-msft , the querie works for the first 1000 lines, I believe that this querie is constructed by the joined querie PBI resource, and the original tables that compose this joined table doesnt show the error, neither when a set the profile to show all data.

best regards;

[mwegener]

Hi @Anonymous ,

Try the following steps for more information.

Expand the Column Profile in the lower left corner of Power Query.

See if you can get the native query from the query.

[Anonymous]

Hi @mwegener , I've noticed that this joined querie shows the error below in all collums when I set profile to show all data and a red line is also displayed, when the profile is set to first 1000 lines the error is not showed.

I got the native querie but it is too long to past here, but I don't believe that is a querie problem because it works for the first 1000 lines and worked in previes release of PBI Desktop that I was using before update to the last release.  Coud it be a problem in this release with the resource below that was used to join two tables?

Thanks a lot.

[mwegener]

Hi @Anonymous ,

have you tried the Power BI Desktop September release?

[Anonymous]

@mwegener , I was using August Release, but I updated to september and the same issue is happening.

thanks.

[mwegener]

Hi @Anonymous ,

i think you need to try to get more information about the error.

In the Youtube video "Get more info with Power Query tracing in Power BI Desktop" Adam Saxton shows how he deals with such a problem.

https://www.youtube.com/watch?v=4QJdCI6xmaA

Alternatively, you can open a support ticket with your Power BI Pro license.

https://powerbi.microsoft.com/en-us/support/

[LudFel]

I had a similar error, and managed to fix it, deleting the duplicate lines.
For some reason when you have a column with duplicate values ​​the error occurred.