PowerBI Connector ODBC error when the column name has open bracket character (

poonhs-esquel · June 8, 2018, 8:40am

Hi,

I got a DataSource.Error: ODBC: ERROR [HY000] [Dremio][Connector] (1040) Dremio failed to execute the query when i connect my Azure MS SQL Server Table View with open bracket character ( in the column name. The column name is “Factory Name(Chinese Name)”. However the query script wrongly select “Factory Name(__Chinese Name)”, so there is an ERROR.

Think it is a bug in Dremio connector. please see if it can be fixed. Thanks,

In the Dremio UI SQL query, there is no such issue and i can query the data without any problem. However, when i use Dremio Connector in Power BI, it got error.

poonhs-esquel · June 8, 2018, 8:41am

Error log attached:
DataSource.Error: ODBC: ERROR [HY000] [Dremio][Connector] (1040) Dremio failed to execute the query: select “Factory Code”,
“Factory Name”,
“Factory Name( Chinese Name)”
from “xxxxxxx.xxxxxxxx.dbo”.“V_Factory”
[30029]Query execution error. Details:[
VALIDATION ERROR: Column ‘Factory Name( Chinese Name)’ not found in any table

SQL Query select “Factory Code”,
“Factory Name”,
“Factory Name( Chinese Name)”
from “xxxxxxx.xxxxxxxx.dbo”.“V_Factory”
startLine 3
startColumn 5
endLine 3
endColumn 33

[Error Id: cec3c3f0-aa5a-4331-ad08-d30c8265dd8f on localhost:31010]

(org.apache.calcite.runtime.CalciteContextException) From line 3, column 5 to line 3, column 33: Column ‘Factory Name( Chinese Name)’ not found in any table
sun.reflect.GeneratedConstructorAccessor207.newInstance():-1
sun.reflect.DelegatingConstructorAccessorImpl.newInstance():-1
java.lang.reflect.Constructor.newInstance():-1
org.apache.calcite.runtime.Resources$ExInstWithCause.ex():463
org.apache.calcite.sql.SqlUtil.newContextException():798
org.apache.calcite.sql.SqlUtil.newContextException():786
org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError():4080
org.apache.calcite.sql.validate.EmptyScope.findQualifyingTableName():104
org.apache.calcite.sql.validate.DelegatingScope.findQualifyingTableName():173
org.apache.calcite.sql.validate.ListScope.findQualifyingTableName():131
org.apache.calcite.sql.validate.DelegatingScope.fullyQualify():220
org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visit():4579
org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visit():4561
org.apache.calcite.sql.SqlIdentifier.accept():334
org.apache.calcite.sql.validate.SqlValidatorImpl.expand():4254
org.apache.calcite.sql.validate.SqlValidatorImpl.expandSelectItem():402
org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList():3581
org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect():3088
org.apache.calcite.sql.validate.SelectNamespace.validateImpl():60
org.apache.calcite.sql.validate.AbstractNamespace.validate():84
org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():910
org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery():891
org.apache.calcite.sql.SqlSelect.validate():208
org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression():866
org.apache.calcite.sql.validate.SqlValidatorImpl.validate():577
com.dremio.exec.planner.sql.SqlConverter.validate():172
com.dremio.exec.planner.sql.handlers.PrelTransformer.validateNode():170
com.dremio.exec.planner.sql.handlers.PrelTransformer.validateAndConvert():159
com.dremio.exec.planner.sql.handlers.PrelTransformer.validateAndConvert():155
com.dremio.exec.planner.sql.handlers.query.NormalHandler.getPlan():43
com.dremio.exec.planner.sql.handlers.commands.HandlerToExec.plan():66
com.dremio.exec.work.foreman.AttemptManager.run():290
java.util.concurrent.ThreadPoolExecutor.runWorker():-1
java.util.concurrent.ThreadPoolExecutor$Worker.run():-1
java.lang.Thread.run():-1
Caused By (org.apache.calcite.sql.validate.SqlValidatorException) Column ‘Factory Name( Chinese Name)’ not found in any table
sun.reflect.GeneratedConstructorAccessor206.newInstance():-1
sun.reflect.DelegatingConstructorAccessorImpl.newInstance():-1
java.lang.reflect.Constructor.newInstance():-1
org.apache.calcite.runtime.Resources$ExInstWithCause.ex():463
org.apache.calcite.runtime.Resources$ExInst.ex():572
org.apache.calcite.sql.SqlUtil.newContextException():798
org.apache.calcite.sql.SqlUtil.newContextException():786
org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError():4080
org.apache.calcite.sql.validate.EmptyScope.findQualifyingTableName():104
org.apache.calcite.sql.validate.DelegatingScope.findQualifyingTableName():173
org.apache.calcite.sql.validate.ListScope.findQualifyingTableName():131
org.apache.calcite.sql.validate.DelegatingScope.fullyQualify():220
org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visit():4579
org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visit():4561
org.apache.calcite.sql.SqlIdentifier.accept():334
org.apache.calcite.sql.validate.SqlValidatorImpl.expand():4254
org.apache.calcite.sql.validate.SqlValidatorImpl.expandSelectItem():402
org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList():3581
org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect():3088
org.apache.calcite.sql.validate.SelectNamespace.validateImpl():60
org.apache.calcite.sql.validate.AbstractNamespace.validate():84
org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():910
org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery():891
org.apache.calcite.sql.SqlSelect.validate():208
org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression():866
org.apache.calcite.sql.validate.SqlValidatorImpl.validate():577
com.dremio.exec.planner.sql.SqlConverter.validate():172
com.dremio.exec.planner.sql.handlers.PrelTransformer.validateNode():170
com.dremio.exec.planner.sql.handlers.PrelTransformer.validateAndConvert():159
com.dremio.exec.planner.sql.handlers.PrelTransformer.validateAndConvert():155
com.dremio.exec.planner.sql.handlers.query.NormalHandler.getPlan():43
com.dremio.exec.planner.sql.handlers.commands.HandlerToExec.plan():66
com.dremio.exec.work.foreman.AttemptManager.run():290
java.util.concurrent.ThreadPoolExecutor.runWorker():-1
java.util.concurrent.ThreadPoolExecutor$Worker.run():-1
java.lang.Thread.run():-1

]
Details:
DataSourceKind=Dremio
DataSourcePath=localhost
OdbcErrors=Table

anthony · June 8, 2018, 12:28pm

Do you have our latest ODBC driver installed? https://www.dremio.com/download/
Have you reviewed & followed our doc on how to integrate with PBI? https://docs.dremio.com/client-applications/microsoft-power-bi.html
Assuming yes to both questions above, it would help to better understand exactly at what point in PBI you run into this issue or what you are exactly doing when this occurs. Is it when you are just trying to connect to a table and preview the data (step #5 from our docs)?

poonhs-esquel · June 10, 2018, 10:13am

Thanks @anthony for your quick response.
Both 1 and 2 are yes. The error message i posted prompts when i try to preview the data in Step 5 from the doc). All the other tables from Dremio can be loaded and previewed using PBI, but just having problem when i am loading a table with column name contains bracket characters from MS SQL server.
Maybe, we can have a test to create a table with column name contains bracket characters, then see if the problem can be reproduced?

anthony · June 10, 2018, 11:40pm

I was able to repro the issue. It seems like PBI adds a space after every ( so therefore a column named “Test(” actually becomes "Test( " in the query. Let me investigate to see if this is something we can fix. Meanwhile, I would recommend you create a VDS on top of that table and rename the column.

superbstreak · June 14, 2018, 10:04am

@poonhs-esquel May I ask which Dremio connector version you are using? (You can check this by going to the ODBC Admin > System DSN > Dremio Connector > Configure)

poonhs-esquel · June 20, 2018, 2:09am

Sorry for late reply. The version is v1.3.14.1043 (64-bit)

superbstreak · June 20, 2018, 5:58am

No worries! Thanks!
There is a newer version of the Dremio Connector v1.3.19 available for download.
Would you mind giving it a try?

Topic		Replies	Views
PowerBI Connector ODBC error when the column name has dot character "."	2	1436	March 8, 2019
PowerBI connector error - Unknown datatype name 'WVARCHAR'	5	2315	March 1, 2019
Getting the error ODBC: ERROR [HY000] [Dremio][Connector] (1040) while applying query after expansion of a merged table	2	3606	February 21, 2019
Querying Excel file through ODBC errors out when "Extract field name" is on	5	1159	August 13, 2021
System Error while querying Dremio in Power BI	1	1330	June 7, 2019

PowerBI Connector ODBC error when the column name has open bracket character (

Related topics