Andre Moraes


SQL Server Compact 3.5 - I've a sdf file created in a .NET windows desktop command line program. How to now consume the data in MS Excel

I can see that under

C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\

I've one DLL called

sqlceoledb35.dll

But I don't have any oledb driver listed when creating an UDL file

How can I consume the data in other apps that are not .NET and developed in house them

Thanks, AM.




Re: SQL Server Compact 3.5 - I've a sdf file created in a .NET windows desktop command line program. How to now consume the data?

ErikEJ


You can create the UDL file by hand, as follows:

Code Snippet

[oledb]
; Everything after this line is an OLE DB initstring
Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Persist Security Info=False;Data Source=C:\Program Files\Microsoft SQL Server Compact Edition\v3.1\SDK\Samples\Northwind.sdf

Then open Excel, and from the Import Data menu point to the udl file.

Select a table to be imported

When Excel prompts for the location of the data, press the "Edit Query..." button.

Remove the double quotes from the table name and press OK and OK.

The data is now available for further work in Excel

(tested with the Customers table in Northwind.sdf)







Re: SQL Server Compact 3.5 - I've a sdf file created in a .NET windows desktop command line program. How to now consume the data?

Andre Moraes

This provider option doesn's appear in the UDL - also the sdf file is a 3.5 version not 3.0 this matter

Thanks, AM.






Re: SQL Server Compact 3.5 - I've a sdf file created in a .NET windows desktop command line program. How to now consume the data?

ErikEJ

You must create the UDL file by hand!

Try using: Microsoft.SQLSERVER.MOBILE.OLEDB.3.5 (not tested yet)






Re: SQL Server Compact 3.5 - I've a sdf file created in a .NET windows desktop command line program. How to now consume the data?

Andre Moraes

Says...

Microsoft Data Link Error

cnn.udl : File cannot be opened. Ensure it is a valid Data Link File

:-(

Using regedit I can't find anything about 3.5

Reg. AM.





Re: SQL Server Compact 3.5 - I've a sdf file created in a .NET windows desktop command line program. How to now consume the data?

ErikEJ

The file should only have three lines, everything after Provider= is on a single line.

Try creating a udl file for sql server, and inspect it with notepad to see what it should look like.

As described above, this works fine with SQL Compact 3.1. I will do testing with 3.5 later - is the error an error from Excel






Re: SQL Server Compact 3.5 - I've a sdf file created in a .NET windows desktop command line program. How to now consume the data?

Andre Moraes

Nope the error is comming when I open the .udf file.

Also checking the register I don't have anything that match the sql compact provider. Indeed I think that the setup is not installing the provider at all.





Re: SQL Server Compact 3.5 - I've a sdf file created in a .NET windows desktop command line program. How to now consume the data?

ErikEJ

OK, on my test VPC is found this OLEDB provider name:

Microsoft.SQLSERVER.CE.OLEDB.3.5

Use that, and you will be able to connect. (You may have to modify the SQL statement to only include some columns, as it appears Excel is unhappy with some data types)






Re: SQL Server Compact 3.5 - I've a sdf file created in a .NET windows desktop command line program. How to now consume the data?

bodhi311

I am trying the same thing and can not get it to work. I am using the following

oledb]
; Everything after this line is an OLE DB initstring
Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.5;Persist Security Info=False;Data Source=C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\SDK\Samples\Northwind.sdf


i keep getting the following error on the udl file: File could not be opened. Ensured it is a valid Data LInk File.

I am trying to get the SQL CE to connect to Excel. Any bump in the right direction would be great. Thanks




Re: SQL Server Compact 3.5 - I've a sdf file created in a .NET windows desktop command line program. How to now consume the data?

ErikEJ

It should look like this (on 3 lines):

Code Block

[oledb]
; Everything after this line is an OLE DB initstring
Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Persist Security Info=False;Data Source=:\Program Files\Microsoft SQL Server Compact Edition\v3.5\SDK\Samples\Northwind.sdf

After some more testing it appears that Excel is only able to deal with the first table in the sdf file

A workable approach for now is described in my sample here: http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1815973&SiteID=1 or you may resort to some of the tools from www.primeworks-mobile.com






Re: SQL Server Compact 3.5 - I've a sdf file created in a .NET windows desktop command line program. How to now consume the data?

bodhi311

Thanks ErikEJ. So if I am understanding you correctly you are saying that Excel has the ability to only deal with the first table in the sdf file. If that is the case, it is truely not possible for excel and sql compact to work together with excel as a push and pull system with out some sort of extensive work around. Ideally i am looking to have excel reference the sdf file and make neccessary changes that would later sync up with a master database. Sql compact seemed like the logical approach because in our situation we require some disconnect.

Is it possible then to allow the above described environment where excel can be used to to read and write to the sdf which would later sync up to a master database

Per your suggestion it is possible to get the data into excel by first programmatically exprting the sdf to a csv and then import the csv file into excel.

Thanks for you time and thoughts





Re: SQL Server Compact 3.5 - I've a sdf file created in a .NET windows desktop command line program. How to now consume the data?

ErikEJ

It sounds like it would be more productive to create an application for manipulating the data in the sdf file, and using RDA or Merge replication to synch with a master database. You could also allow export to Excel for within your app then (in csv format).






Re: SQL Server Compact 3.5 - I've a sdf file created in a .NET windows desktop command line program. How to now consume the data?

bodhi311

Thanks that is what I was figuring... It is just that we are such a heavy Excel based company I was thinking it would be nice to some how incorporate it into Excel rather than building a windows based application to do the manipulation.

Thanks again for your knowledge. You are truly and asset to this community.