Forum Discussion
ODBC Error - Key value in connection string too long
Odbc.Query(
"driver={iSeries Access ODBC Driver};" &
"system=AS400.OURCOMPANY.COM;" &
"dsn=dBASE Files"
,
"SELECT * "&
"FROM AS400DB.JOBSCOPEDB.EPOPERF"
)I can run this query without an issue from within Excel, but in Power BI, I get this error:
Seems like my problem is that the dsn property needs to be "dBase File" in Power BI— although Power Query from Excel will accept
an 's' or not."dBASE Fileblargablarg" because it truncates the string to the first ten characters.SMH.
Hi There,
Just wanted to offer a nice solution I found on another site for the warning "DataSource.Error: ODBC: ERROR [HY000]... The key value in connection string too long.
After resolving it, I propose that you do not assume that it is due to a problem with the connection string, but rather to an error in setting a user name and password for the query.
This is the solution from
mikefiorentinoit from Reddit.comOpen spreadsheet
Click on Data
Click on Queries and Connections
Double - Click on any of the connections
In the Power Query editor, click on File, Options and Settings, Data source settings, Click on dsn="?", Click on edit Permissions, Click edit, update user name and password., Click Save, Ok, Close, Click Close and Load, Close Queries and Connections window. Click on main sheet and click update button.
I hope that this answer can be shared with anyone who requires assistance.
4 Replies
Seems like my problem is that the dsn property needs to be "dBase File" in Power BI— although Power Query from Excel will accept
an 's' or not."dBASE Fileblargablarg" because it truncates the string to the first ten characters.SMH.
Hi jcountryman ,
From your second reply, it seems that you have found the cause of this error.
Have you solved the problem?
If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
Best Regards,
Cherry
For the below syntax (image 1), I get the error as shown in image 2
I am trying to connect to a KLD database and the solution mentioned previously didn't work. Could someone give the exact steps to resolve this error?Image 1:
Image 2:
Thanks
Hi There,
Just wanted to offer a nice solution I found on another site for the warning "DataSource.Error: ODBC: ERROR [HY000]... The key value in connection string too long.
After resolving it, I propose that you do not assume that it is due to a problem with the connection string, but rather to an error in setting a user name and password for the query.
This is the solution from
mikefiorentinoit from Reddit.comOpen spreadsheet
Click on Data
Click on Queries and Connections
Double - Click on any of the connections
In the Power Query editor, click on File, Options and Settings, Data source settings, Click on dsn="?", Click on edit Permissions, Click edit, update user name and password., Click Save, Ok, Close, Click Close and Load, Close Queries and Connections window. Click on main sheet and click update button.
I hope that this answer can be shared with anyone who requires assistance.
