SQL Express 2008 Export To SQL Server CE 3.5 or 4.0?

I've never worked with CE, but it seems to be a nice alternative to it's big brother.

Q. How do I export existing SQL Express 2008 database to To SQL Server CE 3.5 or 4.0?

Q. Are there any restrictions changing to CE? (db size, etc...)
pointemanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BartVxCommented:
Hi pointeman,

To migrate your database, check out this tool: http://www.codeproject.com/KB/database/SqlCompactCoptUtility.aspx

For the second part of your question: CE databases are limited to 4GB. You can find the differences between SQL Server and CE here:

http://msdn.microsoft.com/en-us/library/bb896140.aspx

and more conceptual information here:

http://msdn.microsoft.com/en-us/library/ms173037.aspx

0
Mark WillsTopic AdvisorCommented:
OK, good questions :)  I am a bit of a fan of SQL Compact Edition, and have used it since the very early days...

Only recently has MS started to provide the right kind of support within their development platform icluding SSMS, but not completely, and not seamlessly.

You see compact is a different engine altogether. So, some of the feature we see and use in the big-brother versions of SQL Server are simply not there. But then again, the Compact edition doesnt really need any installs. Just copy a few files and you are up and running.

One such area lacking from the Server editions are the tools that enable things like Data import and export. So, migrating data can be a bit intense, but they do support synchronisation and replication.

Similarly some of the structures we might be used to in the Server version - like views and stored procedures simply dont exist. So, you really do need to consider it at the lowest common denominator in terms of (lack of) sophistication - so - the SQL query you write for CE will work for SQL Server, but not the other way around.

You can read (and compare) editions here : http://www.microsoft.com/sqlserver/en/us/editions/compact.aspx

You can read about the support in VS2010 (SP1) here : http://weblogs.asp.net/scottgu/archive/2011/01/11/vs-2010-sp1-and-sql-ce.aspx and http://blogs.msdn.com/b/sqlservercompact/archive/2011/03/15/sql-server-compact-4-0-tooling-support-in-visual-studio-2010-sp1-and-visual-web-developer-express-2010-sp1.aspx

You can connect to the sdf in SSMS and view the data. You can even copy and paste from a table and run queries within SSMS.

Now, in terms of getting data from Express to Compact then it is a bit of hard work and depends a bit on whether or not it is a once off, or recurring requirement.

Back in the early days we had to write our own import and export to synch and share data - and have kept those routines running.

Now, if starting from scratch I would be inclined to pickup one of the many already written utilities and tools - some of which are commercial such as http://www.dbnetcopy.com/dbnetcopy/default.aspx?gclid=CK6f55-56qwCFQklpAodGByNJg and some are free, such as : http://visualstudiogallery.msdn.microsoft.com/0e313dfd-be80-4afb-b5e9-6e74d369f7a1 

If you are a developer and wanting some ideas, then, codeproject used to have a great tool : http://www.codeproject.com/KB/database/SqlCompactCoptUtility.aspx?display=Print you can download the source and see how they do it and adapt to your own requirement. And if needing tutorials then you can visit : http://msdn.microsoft.com/en-us/sqlserver/bb219480.aspx (especially tutorial 5 on data).

And if still not satisfied, then look at http://erikej.blogspot.com/2009/04/sql-compact-3rd-party-tools.html 

But...

If writing a commercial application that you do want to grow and be distributable for true multi-user environments, then keep with Express as your starting point...

0
pointemanAuthor Commented:
Great support, more questions:

>>Compact edition doesnt really need any installs. Just copy a few files and you are up and running.
1. So can I copy-n-paste my existing MS SQL CE (C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v3.5) to...
USB drive?
Tablet (Android)?
Netbook?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

pointemanAuthor Commented:
>>To migrate your database, check out this tool: http://www.codeproject.com/KB/database/SqlCompactCoptUtility.aspx

Q. Will the Code Project utility work for SQL Express 2008 & CE 4.0? (Seems to work on SQL 2005 & CE 3.5)
0
Mark WillsTopic AdvisorCommented:
Well, there are a few different binaries, so you need to make sure you are copying the correct ones, and then you also need to make sure they are in the programs path and/or registered with the SFD extension, but there isnt quite the same need as installing and setting up like the Server edition.

Normally you would include the appropriate binaries into your application build. But there is also the "private (xcopy) deployment" which obviously is windows based. Havent tried android so cannot comment.

But the first time you open (ie SqlCeConnection.Open()) the database on a different platform, it will take longer - because it will want to rebuild all the indexes (well, only if indexes exist on character / string columns because of the changes in sort sequences).

Yes, if you use the source code you should be able to recompile and point to 2008 and CE 4.0 but you will need to recompile... The CE versions are not necessarily compatable the same way 2008 can read 2005 and 2000 databases. In SQL Server, it is usually the case that you can go back but not up. With the tool and recompiling the syntax will pretty much stay the same so shouldnt have any problems.

But there are other examples too... http://erikej.blogspot.com/2010/02/how-to-use-exportsqlce-to-migrate-from.html

Now, I must confess I havent moved entirely to Compact 4.0 yet. Mainly because of Windows Mobile (4.0 is not there yet) and I still use a little bit of RDA (which is no longer supported in 4.0 - uses different techniques).

Compact 4.0 is really used for a data store for ASP.NET web applications - hence all the language around web builder and webmatrix. Compact 3.5 is still the one to use for Mobile apps. So, not all compact editions are equal.

You can see the differences here : http://erikej.blogspot.com/2011/01/comparison-of-sql-server-compact-4-and.html (and as you can tell, I am a fan of erikej :) so know the blog pretty well )

If you could tell us the nature of your development without going into too much detail, then we can probably point you more in the right direction. Which might even include the possibility of localDB (part of SQl 2012 or denali).


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pointemanAuthor Commented:
I'm a C# .NET developer, but in this case not developing software. I'm trying to get our code library 'Code Warehouse' http://www.xcca.com/ moved from SQL Express 2008 to CE 3.54.0 for portability sake. Thought about moving toward a windows tablet. We used to run Code Warehouse on .MDB. but our Windows Vista upgrade prohibits this. So we run on Express 2008. That's what wer're trying to accomplish.

:)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.