Solved

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

Posted on 2009-06-28
15
613 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
0
Comment
Question by:rfwoolf
  • 5
  • 4
  • 3
  • +1
15 Comments
 
LVL 36

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 75 total points
ID: 24733780
i guess you know this site ?
and have read all the white papers about it : ?
http://www.microsoft.com/Sqlserver/2005/en/us/compact.aspx

it doesn't have ane case, procedures, triggers, ...
0
 
LVL 13

Author Comment

by:rfwoolf
ID: 24733789
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" .
0
 
LVL 13

Author Comment

by:rfwoolf
ID: 24733797
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. :(
0
 
LVL 36

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 75 total points
ID: 24733971
even i hadn't heard about the compact version yet ...
i don't follow sql server that close, i admit, never came across it before.

yeah looks like you'll have to stay with the express

maybe we should write our own embedded database ...
There is obviously a lot of need for it.
0
 
LVL 13

Author Comment

by:rfwoolf
ID: 24734379
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.
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 24735182
um yeah, i figured this out (after several years)
database independance is a myth !
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 425 total points
ID: 24738128
You can script your express database as a series of inserts into the Compact database, including the create tables...

There is not too many choices unfortunately...

But, if time is of the essence, there is one tool I have previously recommended and used once myself - the last person said it was brilliant. And it comes with source code so you can see the connections etc... http://www.codeproject.com/KB/database/SqlCompactCoptUtility.aspx

A must read : http://msdn.microsoft.com/en-us/library/aa983341.aspx

And there are the compact tutorials : http://msdn.microsoft.com/en-us/sqlserver/bb219480.aspx

0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 22

Expert Comment

by:senad
ID: 24775227
Why don't you import data from the SQL Server Express 2008 using Access ?
0
 
LVL 22

Expert Comment

by:senad
ID: 24775247
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.
0
 
LVL 22

Expert Comment

by:senad
ID: 24775262
access preserves all your stuff and from access import to absolute db...works...
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24775918
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.
0
 
LVL 13

Author Comment

by:rfwoolf
ID: 24867797
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.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24868762
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
0
 
LVL 13

Author Comment

by:rfwoolf
ID: 24869319
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
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24869931
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
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now