vendredi 13 février 2015

SQLite3 ODBC and MS OLE DB: How to pass Property parameter during connect?

The Issue:


I am trying to import an SQLite database into Power Pivot ('PP') via the SQLite3 ODBC and MS OLE DB for ODBC. I have solved the first hurdle and managed to import a small SQLite DB into PP.


However, when I tried to import a bigger test SQLite DB with 2001 columns/fields, PP reported: No tables were found in the data source. Check the connection information and login credentials.


I have traced it to the cut-off between 2000 fields/columns (which imported OK) and 2001 fields (which gave the above error). I suspect the reason is due to SQLite's default Max_Column. Quoting the official SQLite website:



The default setting for SQLITE_MAX_COLUMN is 2000. You can change it at compile time to values as large as 32767.



Question:


How do I set the maximum column per table parameter for SQLite3 ODBC and/or MS OLE DB for ODBC? I am using the 64-bit version of SQLite3 ODBC 0.9991 (latest version).


What I've tried but didn't work:


Reading Microsoft's article and this, I tried adding Extended Properties:"KAGPROP_MAXCOLUMNSINTABLE=5000" into the Connection String, but the following error occurred when I tested the connection:


enter image description here


I tried various permutations of the above line, e.g. KAGPROP_MAXCOLUMNSINTABLE=5000, Extended Properties:KAGPROP_MAXCOLUMNSINTABLE=5000, etc. but none worked.


I went through the SQLiteODBC Documentation - unfortunately, I still could not find the answer.


Would truly appreciate if the experts at SO can assist. Thanks in advance!


Aucun commentaire:

Enregistrer un commentaire