Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Luna

Can't get information from KISAM databases just the tables

Submitted by on ‎04-04-2017 02:36 AM

Hello!,


I'm writing because I keep getting this error since the las update.

 

I'm connecting to a KISAM database and Power Bi actually does it, however when I try to get the information from the tables the following error pops up.


powerbi

 

In english the error says "driver does not support this parameter".

 

The weird thing is that when I do exactly the same using Microsoft Access 2010 instead of Power Bi, it works perfectly.

 

Thanks for your time.

Status: Needs Info
See more ideas labeled with:
6 Comments (6 New)
Comments
v-qiuyu-msft
Community Support
Community Support
‎04-05-2017 07:19 PM

Hi @Luna,

 

Are MS Access and PBI Desktop installed on the same machine? What's the version of desktop?

 

Could you please collect an ODBC trace while performing the operation in question. ODBC traces can be collected as follows:

 

1) Exit Power BI Desktop.
2) Run the ODBC Data Sources Administrator matching the bitness of the Power BI Desktop installation.
3) Select the Tracing tab.
4) Note the Log File Path or configure a different path that's accessible to your user account.
5) Click "Start Tracing Now" and then OK.
6) Start Power BI Desktop and perform the operation that doesn't work correctly.
7) Close Power BI Desktop
😎 Run the ODBC Administrator again and go back to the Tracing tab, then click Stop Tracing Now
9) Collect the log file generated at the Log File path and send it.

 

Best Regards,
Qiuyun Yu

Vicky_Song
Impactful Individual
Impactful Individual
‎04-05-2017 07:46 PM
Status changed to: Needs Info
 
Luna
New Member
‎04-10-2017 03:01 AM

Hello!

 

Yes MS Access and PBI Desktop are installed on the same machine (Abril Update of PBI Desktop)

 

The sql.log is quite heavy so I used zippyshare to share it (if I have to use another method please let me know).

 

LINK: http://www33.zippyshare.com/v/wuJDP8zb/file.html

 

( Here's the sql.log for MS Access 2010 which works fine: http://www8.zippyshare.com/v/njh1XHl6/file.html)

 

However the most interesting part of the PBI Desktop sql.log is the last part since that's where I get the following error (I pasted that part here):

 

 

pbi1.PNG

"driver does not support this parameter".

 

 

Microsoft.Mashu 13d8-152c	ENTER SQLColAttributeW 
		SQLHSTMT            0x0000000EFAE4DB50
		SQLSMALLINT                  6 
		SQLSMALLINT                  3 <unknown>
		SQLPOINTER         0x0000000000000000
		SQLSMALLINT                  0 
		SQLSMALLINT *       0x0000000EFABEC470
		SQLPOINTER          0x0000000EFABEC410

Microsoft.Mashu 13d8-152c	EXIT  SQLColAttributeW  with return code 0 (SQL_SUCCESS)
		SQLHSTMT            0x0000000EFAE4DB50
		SQLSMALLINT                  6 
		SQLSMALLINT                  3 <unknown>
		SQLPOINTER         0x0000000000000000
		SQLSMALLINT                  0 
		SQLSMALLINT *       0x0000000EFABEC470 (0)
		SQLPOINTER          0x0000000EFABEC410 (128)

Microsoft.Mashu 13d8-152c	ENTER SQLColAttributeW 
		SQLHSTMT            0x0000000EFAE4DB50
		SQLSMALLINT                 12 
		SQLSMALLINT                  3 <unknown>
		SQLPOINTER         0x0000000000000000
		SQLSMALLINT                  0 
		SQLSMALLINT *       0x0000000EFABEC470
		SQLPOINTER          0x0000000EFABEC410

Microsoft.Mashu 13d8-152c	EXIT  SQLColAttributeW  with return code 0 (SQL_SUCCESS)
		SQLHSTMT            0x0000000EFAE4DB50
		SQLSMALLINT                 12 
		SQLSMALLINT                  3 <unknown>
		SQLPOINTER         0x0000000000000000
		SQLSMALLINT                  0 
		SQLSMALLINT *       0x0000000EFABEC470 (0)
		SQLPOINTER          0x0000000EFABEC410 (1)

Microsoft.Mashu 13d8-152c	ENTER SQLSetStmtAttrW 
		SQLHSTMT            0x0000000EFAE4DB50
		SQLINTEGER                  26 <SQL_ATTR_ROWS_FETCHED_PTR>
		SQLPOINTER          0x0000000000000000
		SQLINTEGER                   0 

Microsoft.Mashu 13d8-152c	EXIT  SQLSetStmtAttrW  with return code 0 (SQL_SUCCESS)
		SQLHSTMT            0x0000000EFAE4DB50
		SQLINTEGER                  26 <SQL_ATTR_ROWS_FETCHED_PTR>
		SQLPOINTER          0x0000000000000000
		SQLINTEGER                   0 

Microsoft.Mashu 13d8-152c	ENTER SQLFetch 
		HSTMT               0x0000000EFAE4DB50

Microsoft.Mashu 13d8-152c	EXIT  SQLFetch  with return code 0 (SQL_SUCCESS)
		HSTMT               0x0000000EFAE4DB50

Microsoft.Mashu 13d8-152c	ENTER SQLGetData 
		HSTMT               0x0000000EFAE4DB50
		UWORD                        1 
		SWORD                       -8 <SQL_C_WCHAR>
		PTR                 0x0000000E8183F448 
		SQLLEN                  2048
		SQLLEN *            0x0000000EFABEC190

Microsoft.Mashu 13d8-152c	EXIT  SQLGetData  with return code 0 (SQL_SUCCESS)
		HSTMT               0x0000000EFAE4DB50
		UWORD                        1 
		SWORD                       -8 <SQL_C_WCHAR>
		PTR                 0x0000000E8183F448 <Invalid string length!> [-1]
		SQLLEN                  2048
		SQLLEN *            0x0000000EFABEC190 (-1)

Microsoft.Mashu 13d8-152c	ENTER SQLGetData 
		HSTMT               0x0000000EFAE4DB50
		UWORD                        2 
		SWORD                       -8 <SQL_C_WCHAR>
		PTR                 0x0000000E8183F448 
		SQLLEN                  2048
		SQLLEN *            0x0000000EFABEC190

Microsoft.Mashu 13d8-152c	EXIT  SQLGetData  with return code 0 (SQL_SUCCESS)
		HSTMT               0x0000000EFAE4DB50
		UWORD                        2 
		SWORD                       -8 <SQL_C_WCHAR>
		PTR                 0x0000000E8183F448 <Invalid string length!> [-1]
		SQLLEN                  2048
		SQLLEN *            0x0000000EFABEC190 (-1)

Microsoft.Mashu 13d8-152c	ENTER SQLGetData 
		HSTMT               0x0000000EFAE4DB50
		UWORD                        3 
		SWORD                       -8 <SQL_C_WCHAR>
		PTR                 0x0000000E8183F448 
		SQLLEN                  2048
		SQLLEN *            0x0000000EFABEC190

Microsoft.Mashu 13d8-152c	EXIT  SQLGetData  with return code 0 (SQL_SUCCESS)
		HSTMT               0x0000000EFAE4DB50
		UWORD                        3 
		SWORD                       -8 <SQL_C_WCHAR>
		PTR                 0x0000000E8183F448 [      56] "AK_B1_TiposArchivoDocumentos"
		SQLLEN                  2048
		SQLLEN *            0x0000000EFABEC190 (56)

Microsoft.Mashu 13d8-152c	ENTER SQLGetData 
		HSTMT               0x0000000EFAE4DB50
		UWORD                        4 
		SWORD                       -8 <SQL_C_WCHAR>
		PTR                 0x0000000E8183F448 
		SQLLEN                  2048
		SQLLEN *            0x0000000EFABEC190

Microsoft.Mashu 13d8-152c	EXIT  SQLGetData  with return code 0 (SQL_SUCCESS)
		HSTMT               0x0000000EFAE4DB50
		UWORD                        4 
		SWORD                       -8 <SQL_C_WCHAR>
		PTR                 0x0000000E8183F448 [      24] "DocumentType"
		SQLLEN                  2048
		SQLLEN *            0x0000000EFABEC190 (24)

Microsoft.Mashu 13d8-152c	ENTER SQLGetData 
		HSTMT               0x0000000EFAE4DB50
		UWORD                        5 
		SWORD                      -25 <SQL_C_SBIGINT>
		PTR                 <unknown type>
		SQLLEN                  2048
		SQLLEN *            0x0000000EFABEC190

Microsoft.Mashu 13d8-152c	EXIT  SQLGetData  with return code -1 (SQL_ERROR)
		HSTMT               0x0000000EFAE4DB50
		UWORD                        5 
		SWORD                      -25 <SQL_C_SBIGINT>
		PTR                 <unknown type>
		SQLLEN                  2048
		SQLLEN *            0x0000000EFABEC190

		DIAG [HYC00] [Microsoft][Administrador de controladores ODBC] El controlador no admite este parámetro (0) 

Microsoft.Mashu 13d8-152c	ENTER SQLGetDiagRecW 
		SQLSMALLINT                  3 <SQL_HANDLE_STMT>
		SQLHANDLE           0x0000000EFAE4DB50
		SQLSMALLINT                  1 
		SQLWCHAR *          0x0000000EFABEBE70
		SQLINTEGER *        0x0000000EFABEC044
		SQLWCHAR *          0x0000000E986CD390 
		SQLSMALLINT               1024 
		SQLSMALLINT *       0x0000000EFABEC040

Microsoft.Mashu 13d8-152c	EXIT  SQLGetDiagRecW  with return code 0 (SQL_SUCCESS)
		SQLSMALLINT                  3 <SQL_HANDLE_STMT>
		SQLHANDLE           0x0000000EFAE4DB50
		SQLSMALLINT                  1 
		SQLWCHAR *          0x0000000EFABEBE70 [       5] "HYC00"
		SQLINTEGER *        0x0000000EFABEC044 (0)
		SQLWCHAR *          0x0000000E986CD390 [      88] "[Microsoft][Administrador de controladores ODBC] El controlador no admite este par\ffmetro"
		SQLSMALLINT               1024 
		SQLSMALLINT *       0x0000000EFABEC040 (88)

Microsoft.Mashu 13d8-152c	ENTER SQLGetDiagRecW 
		SQLSMALLINT                  3 <SQL_HANDLE_STMT>
		SQLHANDLE           0x0000000EFAE4DB50
		SQLSMALLINT                  2 
		SQLWCHAR *          0x0000000EFABEBE70
		SQLINTEGER *        0x0000000EFABEC044
		SQLWCHAR *          0x0000000E986CD390 
		SQLSMALLINT               1024 
		SQLSMALLINT *       0x0000000EFABEC040

Microsoft.Mashu 13d8-152c	EXIT  SQLGetDiagRecW  with return code 100 (SQL_NO_DATA_FOUND)
		SQLSMALLINT                  3 <SQL_HANDLE_STMT>
		SQLHANDLE           0x0000000EFAE4DB50
		SQLSMALLINT                  2 
		SQLWCHAR *          0x0000000EFABEBE70
		SQLINTEGER *        0x0000000EFABEC044
		SQLWCHAR *          0x0000000E986CD390 
		SQLSMALLINT               1024 
		SQLSMALLINT *       0x0000000EFABEC040

Microsoft.Mashu 13d8-152c	ENTER SQLMoreResults 
		HSTMT               0x0000000EFAE4DB50

Microsoft.Mashu 13d8-152c	EXIT  SQLMoreResults  with return code -1 (SQL_ERROR)
		HSTMT               0x0000000EFAE4DB50

		DIAG [IM001] [Microsoft][Administrador de controladores ODBC] El controlador no admite esta función (0) 

Microsoft.Mashu 13d8-152c	ENTER SQLFreeHandle 
		SQLSMALLINT                  3 <SQL_HANDLE_STMT>
		SQLHANDLE           0x0000000EFAE4DB50

Microsoft.Mashu 13d8-152c	EXIT  SQLFreeHandle  with return code 0 (SQL_SUCCESS)
		SQLSMALLINT                  3 <SQL_HANDLE_STMT>
		SQLHANDLE           0x0000000EFAE4DB50

Microsoft.Mashu 13d8-152c	ENTER SQLDisconnect 
		HDBC                0x0000000EFAE4A4B0

Microsoft.Mashu 13d8-152c	EXIT  SQLDisconnect  with return code 0 (SQL_SUCCESS)
		HDBC                0x0000000EFAE4A4B0

Microsoft.Mashu 13d8-152c	ENTER SQLFreeHandle 
		SQLSMALLINT                  2 <SQL_HANDLE_DBC>
		SQLHANDLE           0x0000000EFAE4A4B0

Microsoft.Mashu 13d8-152c	EXIT  SQLFreeHandle  with return code 0 (SQL_SUCCESS)
		SQLSMALLINT                  2 <SQL_HANDLE_DBC>
		SQLHANDLE           0x0000000EFAE4A4B0

 

 

AK_B1_TiposArchivosDocumentos is the name of the table I used for this troubleshooting.

 

By the way, since the march update there's no way to have PBI Desktop in Spanish, only English.

paulie_m
New Member
‎07-04-2017 02:40 AM

I had the same problem, eventually I wrote a program that grabs the data from Kisam via ADO and then uses BCP to upload the same data into an Azure SQL Database. Work's much better than querying KISAM directly.

Anonymous
Not applicable
‎01-03-2018 11:22 PM

Hello @Luna,

 

You can solve the problem by entering the SQL statement instead of selecting the table. Some of the columns in Autoline are not correctly recognized by Power BI with the KISAM driver. I encounterd the problems with numeric and integer columns.
By adding the ABS function, the absolute value will be returned and the problem was solved for me.

Step by step:

1. Click on "New  Source" en select on ODBC

2. If you use a DSN, select the DSN or else fill in the connection string like I did.

3. Click on "Advanced options".

4. Fill in your SQL Statement.

 

snip_20180104081559.png

To recognize which columns need the ABS function is to add the columns one by one.

 

Hope this helps you

 

 

 




 

Dejv
Regular Visitor
‎03-18-2022 02:22 AM

@paulie_m would you please be able to share the script via private message?

Idea Statuses