SQL Server to SQL Server Compact Edition Database Copy Utility

johnnycantcode

Rate me:

4.95/5 (39 votes)

26 Jun 2008BSD2 min read

346.9K

16.8K

123

Create and manage your mobile database using SQL Management Studio and export them to SQL Compact Edition databases

Download source - 89.23 KB

UPDATE

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 an 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 normally use to create and maintain my databases.

Using the Code

Feel free to take a look at the code and offer your most gracious comments. The application uses a wizard to walk you through converting a database. I have also included a normal WINFORM that also does the conversion, but I stopped development on this in lieu of the wizard, so the form is incomplete. I will be maintaining this code and writing more about it on my blog at www.JohnnyCantCode.com. You can find the original post here.

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, therefore this utility does not copy these.

History

1.0 Initial release

License

This article, along with any associated source code and files, is licensed under The BSD License

Written By

johnnycantcode

Software Developer (Senior) Gologic Tech LLC.

United States

I work as an independent software architect and senior developer. I have worked on many large enterprise projects as well as small single user applications.

Comments and Discussions

Index was outside the bounds of the array.

jame0130-Jun-15 22:46

jame01

30-Jun-15 22:46

hi ..
anyone can help me plz im getting this error in line 75
it saying "

Quote:
Index was outside the bounds of the array.

any help plz

Import or Convert a mdf file to sdf

HassanFad29-Jan-15 0:30

HassanFad

29-Jan-15 0:30

Hi,

Thanks for pointing us to the download URL on (http://www.johnnycantcode.com/[^])to do the download of this tool, unfortunately this link doesn't work. I get the error: "This page can't be displayed".
Can you please correct this to be able to download this tool?

Thanks in advance.

Best Regards,

Hassan
MVP

.Exe

Member 1028672113-May-14 2:09

Member 10286721

13-May-14 2:09

Dear Jhonny,
Greetings...!
Can you please provide the .exe / the utility (tool) with this post, please.
This would be an added help.

Thanks n regards.

F@!Z

NVarCharMAX support

Khaari5-Nov-13 23:23

Khaari

5-Nov-13 23:23

Also, support for NVarChar(MAX) should be added.

Good job

Massood Khaari

provide .exe file for this project also

Member 942577126-Jul-13 8:11

Member 9425771

26-Jul-13 8:11

I downloaded and try to open in vs, throwing errors. If you can post .exe also, we can use the utillity. Thx.

Great tool - after some manual changes :-)

dolbysys18-Feb-13 21:16

dolbysys

18-Feb-13 21:16

I was searching for a tool like this for some time and when I finally found this here, I was a little bit disappointed that I wasn't able to compile it in VS2010/VS2012.

After the following changes it worked like a charm:

Remove unavailable projects
Remove unavailable referenced assemblies (Microsoft.SqlServer.*)
Adding references to Microsoft.SqlServer.* assemblies
located in %ProgramFiles%\Microsoft SQL Server\100\SDK\Assemblies

Change the implementation of SelectMobileAssembliesDialog.Check35version() as described in comment:

private bool Check35version(string path)
{
  if (String.IsNullOrEmpty(path))
    return false;

  FileVersionInfo ver = FileVersionInfo.GetVersionInfo(path);
  if (ver.FileVersion == "3.5.8080.0")
  {
    _Ver35Valid = true;
    return true;
  }
  else
  {
    _Ver35Valid = false;
    return false;
  }
}

See also this comment

Thanks for this piece of work Thumbs Up | :thumbsup:

Re: Great tool - after some manual changes :-)

pmiossec27-Feb-13 12:45

pmiossec

27-Feb-13 12:45

I have done all these changes (and remove version checking do be more open) here :
https://github.com/pmiossec/SQL2SQLCompact[^]

Re: Great tool - after some manual changes :-)

ronan_mc25-Apr-13 13:07

ronan_mc

25-Apr-13 13:07

I downloaded the code from github but I'm getting namespace not found for 'Server' and 'Database' in
SourceCtrl.cs, TableCtrl.cs

Re: Great tool - after some manual changes :-)

pmiossec28-Apr-13 1:43

pmiossec

28-Apr-13 1:43

I think, you should have a problem with the references included with the project. Verify (and if needed update) the reference to the file "C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"

geht nicht!

Member 250914025-Jan-13 4:56

Member 2509140

25-Jan-13 4:56

nach konvertierung in VS.2012 lässt sich das Programm nicht starten Frown | :-(

Re: geht nicht!

dolbysys18-Feb-13 21:20

dolbysys

18-Feb-13 21:20

Nach ein paar kleinen Änderungen kann man das Projekt auch mit VS2010 / VS2012 starten:
See this comment[^]

Mdf----_> sdf and sdf ----> mdf

cjgallardo9629-Oct-12 3:03

cjgallardo96

29-Oct-12 3:03

Hola, una consulta soy nuevo en programación movil y como tal tengo un pequeño problema , tengo una aplicacion de escritorio (sql server 2008 (mdf) y visual basic 2008) y deseo leer desde mi dispositivo movil algunas consultas, pero como mi movil tiene bd en extension sdf....entonces como hago que cuando consulte al servidor de mdf me convierta en simultaneo sdf y cunado quiero hacer una transaccion desde mi celular igual lo guarde como mdf.
Disculpe las molestias y ansioso de sus sabias respuestas.
Atte .-
C. Javier Del Aguila Gallardo

asm.GetType("System.Data.SqlServerCe.SqlCeEngine"); returns null

bradut15-Oct-12 11:25

bradut

15-Oct-12 11:25

Hi,
Thank you for this application: it solves an important need, and I hope i will also be able to use it as well Smile | :)

Unfortunately, I have a problem:
In WizardForm.cs, line 381,

Type type = asm.GetType("System.Data.SqlServerCe.SqlCeEngine");

, I get a null value when I try to copy a database from MS SQL 2008 Express to Compact Edition 3.5 SP2

Could anyone suggest a fix for this please?

Re: asm.GetType("System.Data.SqlServerCe.SqlCeEngine"); returns null

Jeff Wharton2-Jan-13 18:01

Jeff Wharton

2-Jan-13 18:01

The description of the application clearly states that it works for SQL Server 7.0, SQL Server 2000 and SQL Server 2005. This is why you are getting this error. You will need to upgrade the Microsoft.SQLServer.* references to use SQL Server 2008 assemblies.

My vote of 5

SPI24-Aug-12 7:20

SPI

24-Aug-12 7:20

Great. Thanks.

My vote of 5

eyedia27-Apr-12 9:40

eyedia

27-Apr-12 9:40

A big Thanks

My vote of 5

Manoj Kumar Choubey17-Apr-12 2:21

Manoj Kumar Choubey

17-Apr-12 2:21

Nice

My vote of 5

footnote9-Sep-11 8:28

footnote

9-Sep-11 8:28

With a few changes as suggested by earlier commenters, this makes up an automation tool that saves a lot of time. Thanks!

Is there anyone able to make it work with SQL Server 2008 R2?

Ehsan.thabit21-Jun-11 8:14

Ehsan.thabit

21-Jun-11 8:14

Great program

Support for Compact edition v3.5 sp2

jcgk_7713-Apr-11 2:32

jcgk_77

13-Apr-11 2:32

Hi

I just add new version number, so that this program could be able to support SQL Server Compact Edition V3.5 Sp2.

Just open the SQLToSQLCompactCopy-source\Dialogs\SelectMobileAssembliesDialog.cs and replace the current "Check35version()" function with

private bool Check35version(string path)
{
if (String.IsNullOrEmpty(path))
return false;

FileVersionInfo ver = FileVersionInfo.GetVersionInfo(path);
if (ver.FileVersion == "3.5.8080.0")
{
_Ver35Valid = true;
return true;
}
else
{
_Ver35Valid = false;
return false;
}
}

Re: Support for Compact edition v3.5 sp2

Member 849633121-Mar-12 9:40

Member 8496331

21-Mar-12 9:40

Or rather support for both versions:

---8<---
private bool Check35version(string path)
{
    if (String.IsNullOrEmpty(path))
       return false;

    FileVersionInfo ver = FileVersionInfo.GetVersionInfo(path);
    if (ver.FileVersion == "3.5.8080.0" ||
        ver.FileVersion == "3.5.5386.0")
    {
       _Ver35Valid = true;
       return true;
    }
---8<---

Default locateion for: System.Data.SqlServerCe.dll
= C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Desktop
= C:\Program Files\Microsoft SQL Server Compact Edition\v4.0\Desktop

Also a tweek for the References for SQL Server 2012 Express
make ref to theise .DLL's

C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll
C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Management.Sdk.Sfc.dll
C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Smo.dll
C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.SqlEnum.dll

regards

Johnny Can't Code?

Member 78058094-Apr-11 5:43

Member 7805809

4-Apr-11 5:43

johnnycantcode.com appears to have no content... anywhere on the site...

Why does the solution reference projects GLT.Common and GLT.Licensing which are not included, and do not appear to be required?

getting error just-in-time (JIT) debugging instead of this dialog box

psnlakshmi30-Oct-10 1:40

psnlakshmi

30-Oct-10 1:40

HI
I´m trying to copy a SQL Server 2005 DB to a SQL Compact DB using the this tool, everything goes fine but when I do the last step I get this error

See the end of this message for details 

on invoking 
just-in-time (JIT) debugging instead of 

this dialog box.

************** Exception Text 

**************
System.Reflection.TargetInvocationExcept

ion: Exception has been thrown by the 
target of an invocation. ---> 

System.ArgumentException: Invalid value 
for key 'locale identifier'.  ---> 

System.FormatException: Input string was 
not in a correct format.
   at 

System.Number.StringToNumber(String str, 

NumberStyles options, NumberBuffer& 

number, NumberFormatInfo info, Boolean 

parseDecimal)
   at System.Number.ParseInt32(String s, 

NumberStyles style, NumberFormatInfo 
info)
   at 

System.Data.SqlServerCe.ConStringUtil.Se

tIntType(String value, String key)
   --- End of inner exception stack 

trace ---
   at 
System.Data.SqlServerCe.ConStringUtil.Se

tIntType(String value, String key)
   at 

System.Data.SqlServerCe.ConStringUtil.Se
tLocale(Hashtable table)
   at 

System.Data.SqlServerCe.ConStringUtil.Ve
rifyValues(Hashtable values)
   at 

System.Data.SqlServerCe.ConStringUtil.Pa
rseConnectionString(String& 

connectionString)
   at 
System.Data.SqlServerCe.SqlCeEngine.set_

LocalConnectionString(String value)
   at 

System.Data.SqlServerCe.SqlCeEngine..cto

r(String connectionString)
   --- End of inner exception stack 

trace ---
   at 
System.RuntimeMethodHandle._InvokeConstr

uctor(Object[] args, SignatureStruct& 

signature, IntPtr declaringType)
   at 

System.RuntimeMethodHandle.InvokeConstru
ctor(Object[] args, SignatureStruct 

signature, RuntimeTypeHandle 
declaringType)
   at 

System.Reflection.RuntimeConstructorInfo

.Invoke(BindingFlags invokeAttr, Binder 

binder, Object[] parameters, CultureInfo 
culture)
   at 

System.RuntimeType.CreateInstanceImpl(Bi

ndingFlags bindingAttr, Binder binder, 
Object[] args, CultureInfo culture, 

Object[] activationAttributes)
   at 
System.Activator.CreateInstance(Type 

type, BindingFlags bindingAttr, Binder 
binder, Object[] args, CultureInfo 

culture, Object[] activationAttributes)
   at GLT.SqlCopy.WizardForm.DoCopy()
   at 

GLT.SqlCopy.WizardForm.UpdateWizard()
   at 

GLT.SqlCopy.WizardForm.btnNext_Click(Obj
ect sender, EventArgs e)
   at 

System.Windows.Forms.Control.OnClick(Eve
ntArgs e)
   at 

System.Windows.Forms.Button.OnClick(Even
tArgs e)
   at 

System.Windows.Forms.Button.OnMouseUp(Mo
useEventArgs mevent)
   at 

System.Windows.Forms.Control.WmMouseUp(M

essage& m, MouseButtons button, Int32 
clicks)
   at 

System.Windows.Forms.Control.WndProc(Mes
sage& m)
   at 

System.Windows.Forms.ButtonBase.WndProc(
Message& m)
   at 

System.Windows.Forms.Button.WndProc(Mess
age& m)
   at 

System.Windows.Forms.Control.ControlNati

veWindow.OnMessage(Message& m)
   at 

System.Windows.Forms.Control.ControlNati

veWindow.WndProc(Message& m)
   at 

System.Windows.Forms.NativeWindow.Callba
ck(IntPtr hWnd, Int32 msg, IntPtr 

wparam, IntPtr lparam)


************** Loaded Assemblies 

**************
mscorlib
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.1433 

(REDBITS.050727-1400)
    CodeBase: 
file:///C:/WINDOWS/Microsoft.NET/Framewo

rk/v2.0.50727/mscorlib.dll
----------------------------------------
GLT.SqlCopy
    Assembly Version: 1.0.0.0
    Win32 Version: 1.0.0.0
    CodeBase: 

file:///C:/Documents%20and%20Settings/la

xmi/Desktop/SQL2005%20express%20ToSQLCom

pact%20Convert/GLT.SqlCopy.exe
----------------------------------------
System.Windows.Forms
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.1433 

(REDBITS.050727-1400)
    CodeBase: 
file:///C:/WINDOWS/assembly/GAC_MSIL/Sys

tem.Windows.Forms/2.0.0.0__b77a5c561934e

089/System.Windows.Forms.dll
----------------------------------------
System
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.1433 

(REDBITS.050727-1400)
    CodeBase: 
file:///C:/WINDOWS/assembly/GAC_MSIL/Sys

tem/2.0.0.0__b77a5c561934e089/System.dll
----------------------------------------
System.Drawing
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.1433 

(REDBITS.050727-1400)
    CodeBase: 
file:///C:/WINDOWS/assembly/GAC_MSIL/Sys

tem.Drawing/2.0.0.0__b03f5f7f11d50a3a/Sy

stem.Drawing.dll
----------------------------------------
Accessibility
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.1433 

(REDBITS.050727-1400)
    CodeBase: 
file:///C:/WINDOWS/assembly/GAC_MSIL/Acc

essibility/2.0.0.0__b03f5f7f11d50a3a/Acc

essibility.dll
----------------------------------------
System.Configuration
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.1433 

(REDBITS.050727-1400)
    CodeBase: 
file:///C:/WINDOWS/assembly/GAC_MSIL/Sys

tem.Configuration/2.0.0.0__b03f5f7f11d50

a3a/System.Configuration.dll
----------------------------------------
System.Xml
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.1433 

(REDBITS.050727-1400)
    CodeBase: 
file:///C:/WINDOWS/assembly/GAC_MSIL/Sys

tem.Xml/2.0.0.0__b77a5c561934e089/System

.Xml.dll
----------------------------------------
Microsoft.SqlServer.Smo
    Assembly Version: 9.0.242.0
    Win32 Version: 9.00.4035.00
    CodeBase: 

file:///C:/WINDOWS/assembly/GAC_MSIL/Mic

rosoft.SqlServer.Smo/9.0.242.0__89845dcd

8080cc91/Microsoft.SqlServer.Smo.dll
----------------------------------------
Microsoft.SqlServer.ConnectionInfo
    Assembly Version: 9.0.242.0
    Win32 Version: 9.00.4035.00
    CodeBase: 

file:///C:/WINDOWS/assembly/GAC_MSIL/Mic

rosoft.SqlServer.ConnectionInfo/9.0.242.

0__89845dcd8080cc91/Microsoft.SqlServer.

ConnectionInfo.dll
----------------------------------------
System.Data
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.1433 

(REDBITS.050727-1400)
    CodeBase: 
file:///C:/WINDOWS/assembly/GAC_32/Syste

m.Data/2.0.0.0__b77a5c561934e089/System.

Data.dll
----------------------------------------
Microsoft.SqlServer.SmoEnum
    Assembly Version: 9.0.242.0
    Win32 Version: 9.00.4035.00
    CodeBase: 

file:///C:/WINDOWS/assembly/GAC_MSIL/Mic

rosoft.SqlServer.SmoEnum/9.0.242.0__8984

5dcd8080cc91/Microsoft.SqlServer.SmoEnum

.dll
----------------------------------------
System.Transactions
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.1433 

(REDBITS.050727-1400)
    CodeBase: 
file:///C:/WINDOWS/assembly/GAC_32/Syste

m.Transactions/2.0.0.0__b77a5c561934e089

/System.Transactions.dll
----------------------------------------
System.EnterpriseServices
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.1433 

(REDBITS.050727-1400)
    CodeBase: 
file:///C:/WINDOWS/assembly/GAC_32/Syste

m.EnterpriseServices/2.0.0.0__b03f5f7f11

d50a3a/System.EnterpriseServices.dll
----------------------------------------
Microsoft.SqlServer.SqlEnum
    Assembly Version: 9.0.242.0
    Win32 Version: 9.00.4035.00
    CodeBase: 

file:///C:/WINDOWS/assembly/GAC_MSIL/Mic

rosoft.SqlServer.SqlEnum/9.0.242.0__8984

5dcd8080cc91/Microsoft.SqlServer.SqlEnum

.dll
----------------------------------------
Microsoft.SqlServer.BatchParser
    Assembly Version: 9.0.242.0
    Win32 Version: 2005.090.4035.00
    CodeBase: 

file:///C:/WINDOWS/assembly/GAC_32/Micro

soft.SqlServer.BatchParser/9.0.242.0__89

845dcd8080cc91/Microsoft.SqlServer.Batch

Parser.dll
----------------------------------------
System.Data.SqlServerCe
    Assembly Version: 9.0.242.0
    Win32 Version: 3.0.5300.0
    CodeBase: 

file:///C:/WINDOWS/assembly/GAC_MSIL/Sys

tem.Data.SqlServerCe/9.0.242.0__89845dcd

8080cc91/System.Data.SqlServerCe.dll
----------------------------------------

************** JIT Debugging 

**************
To enable just-in-time (JIT) debugging, 

the .config file for this
application or computer (machine.config) 

must have the
jitDebugging value set in the 

system.windows.forms section.
The application must also be compiled 

with debugging
enabled.

For example:

<configuration>
    <system.windows.forms 

jitDebugging="true" />
</configuration>

When JIT debugging is enabled, any 

unhandled exception
will be sent to the JIT debugger 

registered on the computer
rather than be handled by this dialog 
box.

Help me any body?

Note: i downlod the SQLToSQLCompactCopy.zip from
http://www.johnnycantcode.com/page/SQL-Server-to-SQL-Server-Compact-Edition-Database-Copy.aspx[^]

Bad Image Format Exception

i_lonsdale@hotmail.com15-Oct-10 22:43

i_lonsdale@hotmail.com

15-Oct-10 22:43

Hello

Great tool if I could get it to work... Sigh | :sigh:

I get the following exeption thrown when it tries to create the database:

Could not load file or assembly 'file:///C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\sqlcecompact35.dll' or one of its dependencies. The module was expected to contain an assembly manifest.

I am running it on a Vista Home machine with Visual Studio 2008 pro. I have SQL Express 2005 and SQL CE 3.5 sp2

Hope you can help!

Many thanks in advance.
Ian Smile | :)

problem with varbinary

pgarbo17-Aug-10 5:05

pgarbo

17-Aug-10 5:05

There's a small problem with varbinary columns. They are created with length 1. Simple change in wizardForm.cs @472 fixes this issue:

if (datatype == SqlDataType.NVarChar || datatype == SqlDataType.NChar || datatype == SqlDataType.VarBinary) 
sb.Append(" (").Append(col.DataType.MaximumLength.ToString()).Append(") ");

Anyway, software is great! Thanks, Johnny!

garbo

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.