Saturday, August 9, 2008

Generate Script & Export Data From SQL CE (Compact Edition) 3.5

Several months ago I migrated my family financial and expense information from excel file to SQL CE 3.5.
SQL CE is indeed good enough for data entry purpose, but after a while, I felt that it is not good enough for reporting purpose.
So I decided that it's better to use the SQL Server Express instead.

Now the first problem that I had : How to migrate the tables along with the data from SQL CE 3.5 to SQL Server Express?

I have been googling around for a while, but haven't really found a good one for this, which make me realized that there are more ways to get the data from SQL Server to SQL CE but not the other way around duhh :|

So later, I decided to code it myself ;)

When browsing around the net, I found about Information Schema View that is available in SQL Server, and fortunately it is also available in the SQL CE 3.5 ;) (before I found out about the view, I was using Linq Attributes to generate the table creation ouccch)
So now by using the view, I generate the script for the SQL CE 3.5 database file (.sdf).

In summary, the code will :
1. Generate Table Creation Script
2. Generate Insert Statement for the data in all of the tables
3. Generate script to assign the Primary Keys for the tables
4. Generate script to assign the foreign keys / references for the tables

** Update 5. Generate script to assign Indexes for the tables
6. Save the script into the output file

So here's a little peek of how to use the tool, I'm using Northwind.sdf file in the example below :
There are 2 parameters to pass in ;
1. Connection String to the sdf file
2. Output File Name for the script

Note : If you get any access denied when trying to open the Northwind.sdf file, here is why and the solution.

After the Northwind.sql file has been generated :
1. Create the SQL Server database file (.mdf)
2. Open the Northwind.sql script into your SQL Server Management Studio or Visual Studio (Since I'm currently using SQL Epxress 2008 CTP, there is no management studio yet for now) and just execute them, if everything's ok (finger crossed), you will see this kind of message below and you're done :)

Please note :
1. The tool will not generate the database for you, it will only generate the scripts to execute
2. Currently I purposely convert nchar and nvarchar data type to varchar in the table creation script. I don't see any purpose of using them unless if storing unicode characters in the table.
3. I haven't handled Image / BLOB data type for generating data (Insert Statement).

Ok now, stop talking and just give me the download link :P

Here is the executable file :
**Removed: Please get the files from CodePlex site instead.

And here is the code, if you want to see what's running inside :
**Removed: Please get the files from CodePlex site instead.

**Updates: Erik EJ has provided the newer version of this at CodePlex website, I really recommend that you get the files from there. Huge thanks to Erik to make this utility tool better and to make it possible for others to contribute

Hope it helps ;)
and do leave a comment if you have any feedback or find any bugs, Thanks ;)


Todd Fulmino said...

Thanks for the tool - it worked great! One suggestion would be to include the Indexes as well.

Bembeng Arifin said...

Hi Todd,
Thanks for trying the tool ;) and sorry for the late reply, I'll see what I can do for the indexes this weekend ;)

Anonymous said...

Nice tool and very useful one ;)

I had to make some minor changes in the generated script :
'True' -> '1'
'False' -> '0'
nvarchar instead of varchar

Thank you,

Bembeng Arifin said...

Hi Todd,
Added the Generate Indexes to the tool :)
After almost 2 months trying to get my hands to this :P Thanks for the feedback ;)

Hi Florent,
Thanks for the nice comment :)
Glad that it works for you with the changes ;)

Shane Church said...

Wonderful tool! This just saved me a ton of work migrating from a shared SQL Server CE 3.5 database to a Microsoft Sync Services setup. Thanks again!

Bembeng Arifin said...

Hi Shane,
Thanks for the kind words, glad that it helped ;)

Jeremy L. said...

outstanding little tool. thanks so much!

ErikEJ said...

Hi Bembeng, can I reuse your code in a project I'm planning? I would like to enhance your code with the current missing features and make the resulat available as a OpenSource project. The features I am planning to add include: nchar/nvarchar use, ntext and image support (for INSERTs), support for scripting ROWGUIDCOL property, scripting defaults.

Bembeng Arifin said...

Hi Jeremy,
Thanks for the kind words :)

Hi Erik,
Sure, I'll be really glad if you do that, I'm sure that it will give more benefit for everyone. Please feel free to use that :D

Wilder Santa said...

Great application, it saved my day. Thanks!

ErikEJ said...

I have now made an updated version of this code available at: - feel free to join the project and/or contribute patches and report bugs and feature requests.

PapaDocta said...

i have been looking for something like this for ages.. thanks man... i have one problem tho. i have a database about 95MB and tried to generate the sql script but as soon as it gets to the generating the foreign keys step the application crash and i get OutOfMemoryException although i have 3GB of RAM 2GB free!

please help on this issue.

ErikEJ said...

Please address this issue at - Is it possible to upload your file for testing?

PapaDocta said...

thanks ErikEJ

i have tried another PC and finally got it working .. thank you again for this great tool

Greg Robinson said...

Great tool. Thanks for pulling this together and making it available for general use. Who would have though SQLCE has no way to generate a script of the schema...

ph said...

Many Thanks,

works great for export the DDL from a SDF-File.

Anonymous said...

good software :)

autism said...

I am learner in Sql Server 2005. The article is really good and I think it would be really helpful to me in my project. Thanks for sharing such nice post here.

Be Meo said...

i visited your site, it's very nice. Wellcome to my blog:
thank you so much!

C├ęsar Priego said...

Thanks a lot for sharing this!

Anonymous said...

Your tool is very cool and very help full for my project, can you tell me how to create insert statement for selected column of table

Bembeng Arifin said...

Hi, i'm using some information schema view that provide the table/column definitions, you can check this out in the code repository that Erik has kindly created in the codeplex site. Thanks

incion said...

Great post for web developers .I have bookmarked your page for the further reference
Thanks for this valueable post.
Professional Web Design

Anonymous said...

You could also use SDF Viewer to create the script

Peter parker said...

software download full free for your windows

Dylan Mark said...

Download New Version Softwares for Windows

Stephen Hill said...

Nice Method ..... I will be try to use it ..... May be be this will help me....
Download Free Software | Free Download Full Version Software

Thomas Anderson said...

The great post about Generate Script & Export Data, it's a nice post, thanks for share it..
Download Crack Software

Anonymous said...

It's amazing! You save my time :) , Thanks

Russell_L Farrish said...

Thank you for making this awesome and wonderful works of yours!!keep it up!
Full Software

Sara Williams said...

That's honesty. I like it.
crack software | vipre antivirus 2015 crack

ppc experts said...

PPC Expert For Tech Support | inbound calls for tech support, pop up calls for tech support Call at: +91 981-142-0083 ppc management experts ppc call provide by osiel web
PPC Promotion and marketing is very important to get the business for professional company especially for Tech Support & web design company.
Google Adwords For Tech Support
PPC Expert For Tech Support
Inbound Technical Support call | Call at: +91 981-142-0083 | inbound calls for tech support tech support inbound calls A great online marketing company for tech support inbound calls, Inbound Technical Support call by osiel web

ellie san said...

its very helpful, thanks for sharing this
g data internet security 2015 key

Alie Madison said...

Wow, thanks dude for keeping us posted on this interesting blog.
powershape crack | prism video file converter plus code

Tina Tin said...

oh wow!! this is really nice, such a nice piece of work,........
r-studio crack key

Post a Comment