CodeProject: SQL Server to SQL Server Compact Edition Database Copy Utility. Free source code and programming help

Please download the latest version of this tool from JohnnyCantCode.com. I have updated this utility based on some feedback from users.

I added the ability to select the schema you wish to copy. For most databases this will simply be "dbo".

I fixed a bug where the application did not recognize a valid version of "System.Data.SqlServerCe.dll".

Introduction

This utility will copy the schema and data from a normal SQL Server 2000/2005 database and export it to a SQL Server Compact Edition database. The tool supports version 3.1 or 3.5 of SQL Server Compact Edition.

Background

I was working on a mobile application and needed and easy way to manage the table relationships and indexes without having to resort to scripting. I was familiar with using SQL Server Management Studio so I decided that I would write a utility that converted a normal SQL Server database to a mobile database. This way I could continue using the tool I normal use to create and maintain my databases.

Using the code

Points of Interest

This utility will copy Indexes, Primary Keys, Foreign Keys, Table structure and data. SQL Server Compact Edition does not have support for Views, Triggers nor Stored Procedures, therefor this utility does not copy these.

History

You must Sign In to use this message board.


Per page

FirstPrevNext

For SQL Server CE 3.5 SP1

kshakir

11:08 23 Jul '09

For the other SQL Server CE 3.5 SP1 people, I was able to get it to work by modifying SelectMobileAssembliesDialog.cs line 98 to:


            if (ver.FileMajorPart == 3 && ver.FileMinorPart == 5)

Also, I ran into some issue in the FinishCtrl.cs trying to update the progress bar based on the number of rows. As I was only using this for a POC, I didn't debug it (I think my data made it! Wink

and instead hacked lines 58 & 59 to say:


            progressBar1.Maximum = Math.Max( value, max );
            progressBar1.Value = Math.Min( value, max );

Other than that it worked great! Thank you very, very much for developing this tool, and for releasing the source to the community.

Sql 2008 support?

khanricksteele

8:26 29 Apr '09

Seems very polished but I use SQL 2008 express at home. Any chance of adding support for that soon?
Sign In·View Thread·PermaLink	1.50/5

Unknown datatype

Member 1871253

0:38 19 Mar '09

I had this error whiloe trying to upgrade database "The specified data type is not valid. [ Data type (if known) = Timestamp ]" please help me
Sign In·View Thread·PermaLink

Re: Unknown datatype

Member 2651573

7:32 16 Jun '09

Hi, SQL CE doesn't have a timestamp type so there no "obvious" mapping here. You can try to map Timestamp to Binary(8), but new rows won't have new timestamps. You have to do it manually in your code with SQL CE. Francois YACOB.
Sign In·View Thread·PermaLink

Primery Keys are not correct after import to SQL Compact

voellinger

1:51 26 Jan '09

Hy, this tool ist very good, but I have a problem with the primery keys. In all of my tables are one colum with an ID. This ID is the primerykey of this table. If I make an import the ID´s are all minus 1. Have anybody a solution for me?
Sign In·View Thread·PermaLink

Out of memory error

cess

10:36 30 Nov '08

I am trying to copy a 2 GB file to SQL server Compact. The program keeps on reporting an "Out of Memory" exception. There is one large field (ntext type) The file was originally an access database with a memo field that was converted to SQL server express 2008. Program seems to work well except for this problem. Any suggestions would be appreciated.
Sign In·View Thread·PermaLink	1.00/5

there is problem with NVARCHAR(MAX)

Tajniak5

6:51 20 Nov '08

Hi

this tool is really great, i just found out it doesn't work with NVARCHAR(MAX) columns. To make it works, you need to add one more case in WizardForm.cs which can look like this (works for me):

                    foreach (Column col in tbl.Columns)
                    {
                        if (colIdx > 0)
                            sb.Append(", ");

                        sb.Append("[").Append(col.Name).Append("]").Append(" ");
                        int max = 0;
                        switch (col.DataType.SqlDataType)
                        {
                            case SqlDataType.NVarCharMax:
                                max = 4000;
                                col.DataType = new DataType(SqlDataType.NVarChar);
                                col.DataType.MaximumLength = max;
                                break;

I know that in SQL Server Compact 3.5 the maximum value of NVARCHAR data type is 4000, so I set it to that value, I'm not sure about other versions.

Anyway, thanks again for this great tool!

iv been messing with this for a while until i found this.TNX!!!!!

zivzuv

21:37 18 Nov '08

!!!!!!!!!!
Sign In·View Thread·PermaLink

primary key contraint names

mape1082

6:04 19 Aug '08

I was having problems generating ADO Entity model from a ce db generated with this excelent tool:

"error 6003: The item with identity 'Id' already exists in the metadata collection."

I made the following change:

WizardForm
Line 532
sb.Append("ALTER TABLE [").Append(tbl.Name).Append("] ADD CONSTRAINT PK_");
to
sb.Append("ALTER TABLE [").Append(tbl.Name).Append("] ADD CONSTRAINT PK_" + tbl.Name);

Now it works great Cool

!

Thanks!

3.50/5

Re: primary key contraint names

Francois YACOB

7:38 16 Jun '09

Thanks for your modification, it helps me a lot !

Of course, in your patch, we can comment the following loop :

sb.Append("ALTER TABLE [").Append(tbl.Name).Append("] ADD CONSTRAINT PK_" + tbl.Name);
//create the constraint name
/* Francois : Not need to append the primary field name to the constraint !
for (int k = 0; k < pKeys.Count; k++)
{
if (k > 0)
sb.Append("_");

sb.Append(pKeys[k]);
}
*/

Best regards,
François.

This thing rocks!

sholliday

11:32 5 Aug '08

Thanks. I was stuck in 3.1 and 3.5 limbo. I was trying to get a Northwind sdf for a demo I've been writing. Multiple RDBMS Support and the Factory Design Pattern[^] This thing rocks. SqlServerCE isn't so dag-gone hard to work with now with this tool. Thanks!
Sign In·View Thread·PermaLink

Great work!!! only one thing and an Idea for future work

mape1082

7:19 23 Jul '08

Hi, this is Great work, thanks a lot!

I only noticed one thing..

I was copying two tables, between which there was a foreign key relationship. But the program does not run the foreign key creation code if I dont select the Copy Data option in the interface. That misleaded me a bit, but anyway i got it working!

You saved my life! Laugh

BTW, on idea for a future version: Could extracting the script of a given SqlCE be done? I love the Script-To abilities of Mangement Studio but I dont knwo what the MS guys were thinking by not implementing that so handy feature in the CE environment.

5.00/5

Re: Great work!!! only one thing and an Idea for future work

Francois YACOB

7:43 16 Jun '09

Hi, The main procedure DoCopy() has a lot of nested conditions (maybe a little code refactoring will be nice ) and the part with the comment "Now add the FK relationships" is included in the data process copy ! You have to correct and move the FK relationship creation process out of the Data Copy process. François.
Sign In·View Thread·PermaLink

Type Conversion errors

gratajik

14:25 15 Jul '08

So this is probably more of a general issue of moving from SQL to SQL Compact, but maybe you have a suggestion.

We've got a bunch of Small data types on the SQL Server (e.g., SmallDateTime). It appears that Compact only has DateTime.

So... what's the solution for this? I rather not have to turn all our SmallDateTime's to DateTimes (4 bytes to 8 Bytes) on our multi-million row DB.

We actually are planning on using Microsoft.Sync services to keep the DB's in sync, so this is a general problem.

Any suggestions?

I have been able to get around this in WizardForm.cs by adding a type conversion:

     
case SqlDataType.SmallDateTime:
     max = col.DataType.MaximumLength;
     col.DataType = new DataType(SqlDataType.DateTime);
     col.DataType.MaximumLength = max;
     break;

and can probably do the same for the various other small types (looks like SmallMoney is the other one that will cause issues, as SmallInt is present in Compact).

Thanks! (And VERY handy tool!)
-Greg

ON DELETE ... ON UPDATE ...

Hoang Cuong

23:21 12 Jul '08

Your tool is very useful, it help me much.

I have a note that it lacks ON DELETE, ON UPDATE actions when creating FK constraints. I made a slight change in your code and it works fine for me.


void DoCopy()
{
    ...
    							
    string fkSql = "ALTER TABLE [{0}] ADD CONSTRAINT [{1}] FOREIGN KEY([{2}]) REFERENCES [{3}] ([{4}]) ON DELETE {5} ON UPDATE {6} ";
    ...
    string createFKSql = String.Format(fkSql, 
	tbl.Name, 
	fk.Name, 
	"{0}", 
	fk.ReferencedTable, 
	sourceDb.Tables[fk.ReferencedTable].Indexes[fk.ReferencedKey].IndexedColumns[0].Name,
	fk.DeleteAction.ToSql(), fk.UpdateAction.ToSql());

    ...
}

internal static class ForeignKeyActionExtensions
{
	public static string ToSql(this ForeignKeyAction action)
	{
		switch (action)
		{
			case ForeignKeyAction.Cascade: return "CASCADE";
			case ForeignKeyAction.NoAction: return "NO ACTION";
			case ForeignKeyAction.SetDefault: return "SET DEFAULT";
			case ForeignKeyAction.SetNull: return "SET NULL";
		}
		throw new InvalidEnumArgumentException("action", (int)action, typeof(ForeignKeyAction));
	}
}

data transfer

Shaikh Sahrif

20:18 28 Jun '08

i have a project builded using VB6 and SQL Server whoich LAN anabled. I want to copy from all client pc's data daily and dump it to server pc.
Sign In·View Thread·PermaLink

Brilliant

Johnny J.

21:44 26 Jun '08

Absolutely brilliant tool. You can edit SQL Server CE databases directly using the Management Studio, but it's a poor interface to say the least. And there's a lot of things you can't do that you can normally do with SQL Server databases. Just renaming a field in a table is more work than it should be.

This tool is a big help. Now you can manage your compact database in SQL Server and generate the database from that... Great!

What I would like to see next is the ability to synchronize data from your compact database back to the sql server, because let's say you design the database in SQL Server, perhaps fill it with some data. The you export everything to a compact database and run your application that adds further data to the database. Then at some time, you need to change the layout of the database (which of course you want to do in SQL Server Management Studio), but you don't want to lose the data that has been added to the compact database in the mean time... You get my point, I'm sure...

You've got my 5 for this! HOWEVER - I don't like the name of your site... Poke tongue

/Johnny J.

PS: A small but useful feature would be if the program could automatically scan the system for the needed dll's.

Error after conversion.. Opening converted file with SQL Management Studio

mGxAlander

18:46 5 Jun '08

Hi.. Your tools works great and that is really what I need, however I require 3rd party tools to open the .sdf file after conversion

I could not open the .sdf file with SQL Server Management Studio Express

i get the following error

===================================

Cannot connect to C:\temp\GTAccDB.sdf.

===================================

You are trying to access an older version of a SQL Server Compact Edition database. If this is a SQL Server CE 1.0 or 2.0 database, run upgrade.exe. If this is a SQL Server Compact Edition 3.0 or later database, run Compact / Repair. [ Db version = 3505053,Requested version = 3004180,File name = C:\temp\GTAccDB.sdf ] (SQL Server Compact Edition ADO.NET Data Provider)

------------------------------
Program Location:

at System.Windows.Forms.Control.MarshaledInvoke(Control caller, Delegate method, Object[] args, Boolean synchronous)
at System.Windows.Forms.Control.Invoke(Delegate method, Object[] args)
at System.Windows.Forms.Control.Invoke(Delegate method)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

However, when i view in it SQL CEVIEWER From
http://sourceforge.net/project/showfiles.php?group_id=204776

it shows that my version is MS SQL Compact 3.5

Is there anyway to solve this problem?

5.00/5

Re: Error after conversion.. Opening converted file with SQL Management Studio

johnnycantcode

10:34 12 Jun '08

Sql Server Management Studio Express cannot open SQL Compact 3.5 version databases. I believe the new SQL 2008 management tools can open them. If you want to be able to manage your compact database with Sql Server 2005 Management Studio then you will need to convert it to a 3.1 version of the SQL Compact Database. Scott "Johnny" Smith www.johnnycantcode.com
Sign In·View Thread·PermaLink	1.00/5

Not working with SQL2000

oldieman

23:28 2 Jun '08

When trying to copy a SQL2000 database, I get an error message {"Operation not supported on SQL Server 2000."} at line 75 in file TablesCtrl.cs

Possible solution: Build an if block

if (db.CompatibilityLevel != CompatibilityLevel.Version80) 
{
   foreach (Schema schema in db.Schemas)
   ...
}

Because no schema list is generated with SQL Server 2000 the next error is coming up.
File WizardForm.cs line 414
change following line:
Table tbl = sourceDb.Tables[tblName, tableCtrl1.SchemaName];
to

Table tbl = null;
if (tableCtrl1.SchemaName == "")
   tbl = sourceDb.Tables[tblName];
else
   tbl = sourceDb.Tables[tblName, tableCtrl1.SchemaName];

The next problem is with some SqlDataTypes. Not all type are possible in the compact version. Therefore a convertion must be made for this data types (etc. SmallDataTime convert in DateTime).

File WizardForm.cs line 469 insert following case for SqlDateType.SmallDateTime

case SqlDataType.SmallDateTime:
   col.DataType = new DataType(SqlDataType.DateTime);
   break;

Maybe some more convertion are needed.

On the other hand: A great job done - thanks

Eckmar from Germany

Visual Studio

gnassar

3:26 2 Jun '08

What version are you using?
Sign In·View Thread·PermaLink

Re: Visual Studio

johnnycantcode

10:35 12 Jun '08

I am using Visual Studio 2008 currently. Scott "Johnny" Smith www.johnnycantcode.com
Sign In·View Thread·PermaLink

Schema Setting + SqlCe Version

Charl Victor

0:27 13 May '08

Hi,

I had issues copying data from a Sql Server 2005 database. It keeps complaining that it cannot find the table.

I had to change to code to specify the schema when getting the source table:

Table tbl = sourceDb.Tables[tblName, "dbo"];

This seemed to sort out the issue.

Another issue I had was with the version of the SqlCe. There is a specific version (3.0.5207.0) that comes with Sql Server 2005 and the tool was looking for another version. I just added a "OR" statement and it worked great.

Thanks for a great tool. It saved me a lot of work. Smile

charl

1.00/5

Re: Schema Setting + SqlCe Version

johnnycantcode

4:22 13 May '08

Could you provide me with the code you changed as well as the line numbers? If so I will update the source code so everyone will have these changes. Thanks Scott "Johnny" Smith www.johnnycantcode.com
Sign In·View Thread·PermaLink	3.25/5

Re: Schema Setting + SqlCe Version

Charl Victor

6:17 14 May '08

Hi,

These are the changes I made:

WizardForm.cs

Line 105: if (ver.FileVersion == "3.0.5300.0" || ver.FileVersion == "3.0.5207.0")

Line 414, 604, 724: Table tbl = sourceDb.Tables[tblName, "dbo"];

That's all the changes I made and it worked as expected.

I see there are some instances in the MainForm.cs where you also check the file version, but I am not sure if this is getting executed at all.

Regards,
Charl Big Grin

charl

Last Visit: 3:41 21 Nov '09 Last Update: 3:41 21 Nov '09

12 Next »

UPDATE:

Introduction

Background

Using the code

Points of Interest

History