SQL CE 4.0 - Create a new SDF from an existing SQL schema and data

Question

Good Afternoon,

I have a question regarding the periodic re-creation of an SQL CE 4.0 database (.sdf file)

My customer maintains a "pricebook" of parts and prices in an SQL 2008 R2 database.  This data needs to be migrated every few months into a CE database, that is delivered to external users and remote offices that are not otherwise connected to the SQL server.

The customer has previously used a third party tool "DataPort Wizard" from Primeworks to:

1. Create the empty SDF
2. Create/duplicate the empty tables from the SQL schema
3. Populate the tables with existing data from SQL.

They have not been happy with the tool, and were hoping that by moving from SQL CE 3.5 SP1 to 4.0, and adopting VS 2012, that there would be built in tools to do this.

Based on what I have seen from the Server Explorer Data Connection dialog, and ErikEJ's SQL Server Compact Toolbox, I have not been able to find a way to "Create From" a SQL DB to a CE DB.

Am I missing something, or are my only options a 3rd party tool, or write my own tool to read and recreate a schema via the SqlCeEngine.CreateDatabase() method, and populate it programatically?

Thanks in advance,

-Kevin

Answer 1

If you keep SQL CE 3.5, you could use Merge Replication or RDA to create the SDF and then distirbute it, or even let the client app remotely update itself. The drawback is that both options require IIS and neither work with SQL Server Express SKUs as publishers.

---

Alberto Silva Microsoft MVP - Device Application Development - http://msmvps.com/AlbertoSilva moving2u - R&D Manager - http://www.moving2u.pt

Answer 2

Hi, you can do this from the command line as described here: http://erikej.blogspot.dk/2010/02/how-to-use-exportsqlce-to-migrate-from.html

Are you looking for a feature in the Toolbox to do this in a single process - ie extend the "Script SQL Server Database Schema and Data" feature to create a version 4.0 sdf file on the fly => so call it "Export SQL Server to SQL Server Compact 4.0" ??

---

Please mark as answer, if this was it. Visit my SQL Server Compact blog

Answer 3

I have added the feature to the next Toolbox release (release beginning of next year, available as source now, let me know if you would like a special test build)

---

Please mark as answer, if this was it. Visit my SQL Server Compact blog

Answer 4

Thank you very much Erik!

I have been looking at the Export2Sqlce command line tool this morning, (I don't know how I missed it earlier) and the Script Schema and DB option in the Toolbox.  After looking at the code for the command line tool in Codeplex, I was just about to adapt it into a small special purpose custom app specific to my customer.  Basically take your batch script and wrap it in a simple GUI.

The main difference I noticed, was that in the Toolbox, you INCLUDE (check) the table you want to script, and in the Command Line Tool, you EXCLUDE the tables you don't want.  I was going to wrap the batch command using the "include only what you want" approach.

In any case, if you have a test build that includes the "Export SQL Server to SQL Server Compact 4.0" I would love to test it.  I have the ability to test in either VS 2010 Premium or VS 2012 Premium.  I am connecting to a SQL Server 2008 R2, and creating a CE 4.0 db.

Thanks in advance, AND thanks for being such a great resource for CE! I've never worked with CE before this project, and your blog and Toolbox have reallly helped me come up to speed rapidly!

-Kevin

---

Kevin J. McGinnis

Answer 5

I have uploaded a test visx here: http://sqlcetoolbox.codeplex.com/workitem/10644 - please provide any feedback on this page page also.

---

Please mark as answer, if this was it. Visit my SQL Server Compact blog