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

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
LVL 13
rfwoolfAsked:
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.

Geert GOracle dbaCommented:
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
rfwoolfAuthor 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" .
0
rfwoolfAuthor 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. :(
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Geert GOracle dbaCommented:
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
rfwoolfAuthor 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.
0
Geert GOracle dbaCommented:
um yeah, i figured this out (after several years)
database independance is a myth !
0
Mark WillsTopic AdvisorCommented:
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

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
senadCommented:
Why don't you import data from the SQL Server Express 2008 using Access ?
0
senadCommented:
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
senadCommented:
access preserves all your stuff and from access import to absolute db...works...
0
Mark WillsTopic AdvisorCommented:
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
rfwoolfAuthor 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.
0
Mark WillsTopic AdvisorCommented:
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
rfwoolfAuthor 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
0
Mark WillsTopic AdvisorCommented:
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
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.