SQL Server Compact 4.0 SDF file

[Anonymous]

Hi,

Could someone help show me how to connect to an SQL Server Compact 4.0 .sdf file in Qlik Sense Enterprise. I've attached some sample data, in the case it is an issue with the file.

Please note:

- i haven't been able to use the connect32 script because qlik sense is requiring me to use lib connect, and we're not able to change to legacy mode. "CONNECTs other than LIB CONNECT are not available in this script mode."

- I think i need to connect via one of the OLE DB, but in the list of OLE DB connections, i can't find one for SQL Compact 4.0 (is there a way to install a specific new OLE DB connector in Qlik Sense for Comapct 4.0

-I have tried to connect via SQL 2008 R2 and SQL 2012, and use the file source as a data source; however i haven't been able to get 2008 R2 to work with compact 4.0 files (compact 3.5 files seem to work just fine). - even after installing sql compact 4.0 data connections. SQL Server 2012  doesn't support compact sdf files.

-or, is there a particular ODBC connector I can install so that Qlik Sense can link to that?

the above are options i've tried and failed at, if someone is successful with the sdf file attached, could you please let me know how you got it to work in Qlik Sense Enterprise.

[petter]

Have you tried any of these tips:

https://www.connectionstrings.com/sql-server-compact/

[Anonymous]

‌Thanks Petter,

though they look helpful they only seem to be for compact 3.0 and 3.5, whereas my data is compact 4.0. plus I have to use a qlik sense library connection (so I have to use one of the in built qlik ole dB connectors, or install an old dB connector for compact 4.0 that will be picked up in qlik sense (So far I haven’t been able to find one that works, the Microsoft sql compact 4.0 from this link Download Microsoft SQL Server Compact 4.0 from Official Microsoft Download Center  doesn’t seem to add any functionality to qlik sense.

if anyone has used a sdf compact 4.0 file, or can download the attached data in the top post here and tell me how you got it to work that would be fantastic

[petter · Accepted solution]

I found some old notes so here is the recipe to be able to create the right LIB connection.

With your ProductionStorage.sdf I was able to load 70 lines of data:

THE STEPS TO CREATE A CONNECTION:

First create a connection by selecting MS DataShape:

Then fill in the right connection string which is:

Provider=Microsoft.SQLSERVER.CE.OLEDB..0;Data Source=C:\\Users\\Petter\\Downloads\\ProductionStorage.sdf;

Give it an appropriate Name and press Create - DO NOT press Test connection or Load.

Then edit the connection again to correct the connection string which has been mangled when we saved it to include some additonal MS DataShape related stuff that needs to be removed...

Remove the first stuff of the connection string which says "Provider=MSDataShape;Data Source=" and leave the rest which should be the correct complete connection string. Save it again.

Then your'e good to go.

I tested it with the regular Qlik Sense Desktop but I see no reason why it shouldn't work on Qlik Sense Enterprise also.

The "Select Data" works also after having a working LIB Connection:

[Anonymous]

OMG, there it is! it works! thank you so very much Petter. Days and days looking at this, thank you for this solution. very grateful. time for a beer

[Jan-De-Geyter]

https://www.rebasedata.com/convert-sdf-to-csv-online

worked for me to unlock the 10 years of data in my Tanita database 🙂