We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Move SQL Server Express database to SQL Server Compact? (Urgent :D)

Medium Priority
643 Views
Last Modified: 2012-05-07
Hi guys

I have been working about 18 hours straight for a deadline...

I have designed my tables in SQL Server Express 2008, and my application connects to it fine using ADO (in Delphi).
But it needs to be deployed as Compacy Edition which I've never used.
How can I get it to a single-file database?
Where can I get the driver I need to deploy with my application so that I can work with the single-file database?

Any help is appreciated!!! :D
Comment
Watch Question

Geert GOracle dba
CERTIFIED EXPERT
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
hi geert my app isn't using any CASE, procedures, triggers etc. maybe some joins and subselects but that's it.
I managed to try a zillion connection strings from connectionstrings.com and after including some random dlls from the SQL Server Compacy Edition directory, it now allows me to connect and access the database, but now that I'm running my application I've hit some error like "Object was open" .

Author

Commented:
Yup, I've tried to open some queries in my app and it's giving me
the command contained one or more errors. [,,,,,,]

Looks like I'm going to have to install SQL Server Express
What a @#$#ing disappointment,
and there's been zero help on this subject on the net for delphi. :(
Geert GOracle dba
CERTIFIED EXPERT
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Ummm... firebird has a kick-ass embedded database. Trouble is we run one application on SQL Server, and this was going to be its sister application running on a stand-a-lone desktop. So I had this bright ideas that should we ever need to merge the two databases, it would be good if they were from the same family. Unfortunately my bright idea was a bad one.
Geert GOracle dba
CERTIFIED EXPERT
Top Expert 2009

Commented:
um yeah, i figured this out (after several years)
database independance is a myth !
Topic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2010

Commented:
Why don't you import data from the SQL Server Express 2008 using Access ?
CERTIFIED EXPERT
Top Expert 2010

Commented:
Absolute database can easily embedd your database within the exe.
http://www.componentace.com/bde_replacement_database_delphi_absolute_database.htm
Also you can import data from the SQL server easily using ODBC.
CERTIFIED EXPERT
Top Expert 2010

Commented:
access preserves all your stuff and from access import to absolute db...works...
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
SQL Express is compatible, is extensible, is scalable, is for free.

You can even distribute free of charge (if you register). For you, get the SQL Express with Advanced Services, and for distribution, all you need is the runtime. You can also create deployment projects so it is "automatically" installed with your app.  Let me know if you want some more details.

Much easier with Express than Compact.

Author

Commented:
mark_wills> you make it sound like deploying SQL Express is so easy. I insist that it's not, so much so that I've wasted my life trying to do so easily. In fact when I download the installation file from the website to 'deploy' to my clients, even the installation file needs to connect to the internet to grab all the stuff it needs, which quite frankly is @#$#@. Then if I do manage to find a file with everything inside it that I need, I'm looking at about 250mb install file. Plus installing Powershell and .NET 3.5 plus SQL Server Express. I would say that they couldn't really make deploying SQL Server Express any harder if they tried. Not to mention after installation you have to go and open all the ports, forward all the ports, activate the agents.

Quite frankly, I hate SQL Server deployment. The database platform itself is nice, but it's not worth the pain and agony of deploying.
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Can understand... Did not mean to make it sound so easy, but it need not be too difficult either.

There are a few editions of Express, and really should be looking at the runtime which comes down significantly in size to around 80 meg (which is still bigger than simply copying as is the case in Compact).

If there is Express already installed on a machine, then you can use "xcopy install" which is copying across your database.

You can build a custom installer : http://msdn.microsoft.com/en-us/library/bb264562(SQL.90).aspx

And another method as a "prerequisite" application : http://www.codeproject.com/KB/aspnet/Add_Custom_Prerequisite.aspx

I certainly would not be including .Net 3.5 - rather let it be known that it is a requirement. Powershell is required for some of those add-on bits in the "tools" and "advanced services" editions (which franlky, if Compact was / is a consideration, then it really should only be runtime).

Then there is the "utilities" download : http://www.microsoft.com/downloads/details.aspx?FamilyID=FA87E828-173F-472E-A85C-27ED01CF6B02&displaylang=en

So, it is quite possible to build in the SQL Server Engine as part of your deployment, and it doesn't have to be 250meg.

Having developed applications for Mobile users I have used MSDE, Express and Compact. By far the easiest to support is Express. It is compatable with the desktop and backoffice database and does make life considerably easier down the track. And that was my comment about being "much easier", not so much the deploy (where compact 3.5 is simply a copy), but more so living with the consequences.

So, hope it goes well for you, either Compact or Express... And if you don't mind, pop back in and let us know how it goes ? I certainly would enjoy hearing back from you...

Cheers,
Mark Wills

Author

Commented:
Thanks for that Mark...
Unfortunately Compact, although I got it "working" wouldn't support half of my SQL subselects, stuff like
  SELECT Firstname, Lastname, (SELECT ...
and I tried really hard and wasted many hours on that nightmare.

So I had to revisit SQL Express (and encountered the previously mentioned frustration) and I needed to deploy on my boss's laptop in a 3rd world African country with very slow and frustrating internet connectivity. Somehow the 80MB - 120MB install file (plus Powertools and .NET) and the frustration of packaging all of it put me a little off.
So I decided to take the 4-5 hour journey of porting my app away from SQL Server to Firebird. :D
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Well being an old Interbase / Delphi programmer can understand that sentiment.

Compact is a little disappointing in the current level of support. It works quite well, but you must code for that level of database supprot from the get go.

Pity to hear that the packaging of Express was not a good experience.

Cheers,

Mark Wills
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.