topic Re: SQL Server Compact 4.0 SDF file in Connectivity & Data Prep

SQL Server Compact 4.0 SDF file

Anonymous — Tue, 09 Jan 2018 02:38:17 GMT

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.

Re: SQL Server Compact 4.0 SDF file

petter — Tue, 09 Jan 2018 11:18:10 GMT

Have you tried any of these tips:

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

Re: SQL Server Compact 4.0 SDF file

Anonymous — Tue, 09 Jan 2018 12:28:00 GMT

‌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

Re: SQL Server Compact 4.0 SDF file

petter — Tue, 09 Jan 2018 12:34:20 GMT

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:

Re: SQL Server Compact 4.0 SDF file

Anonymous — Tue, 09 Jan 2018 23:13:34 GMT

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

Re: SQL Server Compact 4.0 SDF file

Jan-De-Geyter — Fri, 05 Jun 2020 15:22:06 GMT

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

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