Saturday, August 21, 2010

How to Migrate/Downsize a SQL Server database to SQL Server Compact 4.0 (and 3.5)

Release 3.0.0.6 of my SQL Compact schema and data script utility on Codeplex contains a command line utility, named Export2SqlCe, that allows you to export schema and data from a SQL Server 2005/2008 database in a SQL Compact compatible SQL format.

The Export2SqlCe tool does not create a SQL Compact sdf database file, but just creates a T-SQL script , that you can run with a tool like my SqlCeCmd Codeplex utility or SQL Server Management Studio 2008. This approach gives you the flexibility to modify the script in a text editor before creating the sdf file – but requires an extra step.

Below are the steps required to migrate a SQL Server database (tables and table indexes/constraints only) to a SQL Server Compact database.

First, run Export2SqlCe against your SQL Server database (2005 and 2008 are supported), using a command line similar to:

Export2sqlce "Data Source=(local);Initial Catalog=AdventureworksLT;Integrated Security=True" AW.sqlce

image

This will create a file named C:\aw.sqlce – let’s have a look:

image

This file contains a script to create tables, data and constraints (indexes and foreign keys), all in SQL Server Compact 3.5/4.0 compatible T-SQL script.

Then you can either open the aw.sqlce script in SQL Server Management Studio 2008 or use sqlcecmd to create the sdf file and populate the file based on the script – meaning the whole process can be run from a batch file and completely automated!

(To manage SQL Server 4.0 databases, use SqlCeCmd40.exe, for 3.5 use SqlCeCmd.exe)

First create the database:

sqlcecmd40 -d "Data Source=C:\aw.sdf" -e create

Then run the generated script against the database:

sqlcecmd40 -d "Data Source=C:\aw.sdf" -i aw.sqlce > log.txt

Examine the log.txt to ensure no errors occurred - search for “error code” – please let me know if you encounter any errors, so they can possibly be fixed in an update to the utility.

Entire batch file:

Export2sqlce "Data Source=(local);Initial Catalog=AdventureworksLT;Integrated Security=True" AW.sqlce
sqlcecmd40 -d "Data Source=C:\aw.sdf" -e create







sqlcecmd40 -d "Data Source=C:\aw.sdf" -i aw.sqlce > log.txt



del log.txt




for %%f in (aw*.sqlce) do sqlcecmd -d "Data Source= C:\aw.sdf " -i %%f >> log.txtt

53 comments:

Gee said...

Hello

I am struggling to connect to a sdf file from ssms 2008 - can you give me any advice? I get an error saying the editor is not installed. I have SP1 applied.

Thanks

ErikEJ said...

Try to re-install SSMS 2008 - could you share the exact error messsage?

Unknown said...

I get this error:
>sqlcecmd -d ".\Foo.sdf" -q "select *
form props"
Error: System.DllNotFoundException: Unable to load DLL 'sqlceme35.dll': The specified module could not be found. (Except
ion from HRESULT: 0x8007007E)
at System.Data.SqlServerCe.NativeMethods.DllAddRef()
at System.Data.SqlServerCe.SqlCeConnection..ctor()
at SqlCeCmd.SqlCeCommandHelper..ctor(String connectionString)
at SqlCeCmd.Program.Main(String[] args)

I am using Win Mobile 6 Pro DK.
If I try it with sqlcecmd40, I get:
>sqlcecmd40 -d ".\Foo.sdf" -q "select
* form props"

Unhandled Exception: System.IO.FileNotFoundException: Could not load file or assembly 'System.Data.SqlServerCe, Version=
4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file s
pecified.
File name: 'System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'
at SqlCeCmd.Program.Main(String[] args)

WRN: Assembly binding logging is turned OFF.
To enable assembly bind failure logging, set the registry value [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1.

Note: There is some performance penalty associated with assembly bind failure logging.
To turn this feature off, remove the registry value [HKLM\Software\Microsoft\Fusion!EnableLog].

ErikEJ said...

You must have the SQL Server Compact 3.5 SP2 msi installed on your dekstop for the command line utility to work. SqlCeCmd does not run on Windows Mobile.

Martino Bordin said...

Is it possible to perform the reverse, I mean from sql ce 4 to sql server (express or standard)?

Thanks

ErikEJ said...

Martino: Sure, you can use the ExportSqlCe4.exe utility (http://exportsqlce.codeplex.com) to generates a script dump of your database, that you can run against an empty SQL Server database. You can also use WebMatrix to Migrate to SQL Server from Compact 4.0

Dávid said...

Hi Erik,
Can you provide more detail about the parameters in the example: "Data Source=(local);Initial Catalog=AdventureworksLT;Integrated Security=True" AW.sqlce

Thanks,
David

ErikEJ said...

Sure David:
"Data Source=(local);Initial Catalog=AdventureworksLT;Integrated Security=True" is a SQL Server Connectionstring, that specifies the database to export (see connectionstrings.com for samples)

AW.sqlce is the name (and path) of the file you are exporting to.

Albert said...

Im having a little bit of difficulties creating the .sdf file, this is what I get, I appreciate any help:
C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v3.5>Export2SqlCE.ex
e "Data Source=MIRAK\SQLEXPRESS;Initial Catalog=MiniMobileDB;Integrated Security
=True" MiniMobileDB.sqlce
Generating the tables....
Generating the data....
Generating the primary keys....
Generating the indexes....
Generating the foreign keys....
Sent script to output file(s) : MiniMobileDB.sqlce in 256 ms

C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v3.5>sqlcecmd -d "Da
ta Source=C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v3.5\MiniM
obile.sdf" -e create
Error: System.BadImageFormatException: An attempt was made to load a program wit
h an incorrect format. (Exception from HRESULT: 0x8007000B)
at System.Data.SqlServerCe.NativeMethods.DllAddRef()
at System.Data.SqlServerCe.SqlCeEngine..ctor()
at System.Data.SqlServerCe.SqlCeEngine..ctor(String connectionString)
at SqlCeCmd.SqlCeEngineHelper.Execute(EngineAction action, String newConnecti
onString)
at SqlCeCmd.Program.Main(String[] args)

ErikEJ said...

You need to install the x64 SQL Server Compact 3.5 dektop runtime on your computer.

Albert said...

Completely right thank's a lot :D

Prashanth Joy said...

Hi Erik i have some doubt plz help me..
:-)
i started a Smart Device(Windows Compact Application ) c# project. i want to connect to SQL 2000 Server.. if i try to open connection it showing "Unkown Connection Option in Connection String: initial catalog."

ma code
conn.ConnectionString = "datasource=servername;Initial Catalog=dbname;User ID=uname;Password=pasrd;";

plz tell me about connectionString..


Thank You..

ErikEJ said...

Prashant: Suggest you ask in the MSDN Forum and provide more details/a code sample of what you trying to achieve.

Prashanth Joy said...

Hi,
Hello,

I'm trying to import/Export a table from/To desktop server(SQL 2000) to/from SQL CE database .

I'm not getting clear ideas from net.

Can you Help me..


Thank You....

ErikEJ said...

Prashant: I shall try:
From SQL CE => SQL 2000: use exportsqlce and run the generated script againt the SQL 200 db.

From SQL 2000 => SQL CE: Upgrade the SQL 2000 db to 2005 (Express) and use export2sqlce or accomodate the current exportsqlce code to work with SQL Server 2000 metadata (I would include that contribution in the base code then).

Prashanth Joy said...

Thank You Erik ..

Prashanth Joy said...

Hello Erik..

I have Two Systems one is Tablet PC(OS:Windows CE) and a Server PC(OS:Windows XP) .. , Both connected WiFi ,and My Server PC have connected to a Printer,Now i want to print a document from Tablet PC to my server PC Printer.

1) How Can i get Shared Printer in Windows CE..?
2) or How can i Print a Document, Through Network..?
3)Or How Can i Send a file to My Server PC..?

I hope You Can Help Me Out for my Doubt(Issues)


Thank You....

ErikEJ said...

Parshant: Suggest you ask in the MSDN Compact Framework forum

Prashanth Joy said...

hello

How to sync data from SQL SERVER 2000 to SQL CE 3.5 and vice versa.....

thank you

ErikEJ said...

Prashant: That is not supported by any MS technology I know of, upgrade your SQL Server.

Prashanth Joy said...

Oki .. Thank you erik for your valuable responce... now again i have some doubt.. please dn't mind ok...

I'm developing Smart Device application, In that i have set of statement, which establishes connection with sql Server 2000 Database. When ever I try to open SqlConnection, I’m getting error "Can't find PInvoke DLL 'dbnetlib.dll'.".
My systems OS is WINDOWS CE...
Thank You....

ErikEJ said...

Prashant: Please ask in the MSDN Smart Device Dev forum.

Prashanth Joy said...

Hello Eric


This is my C# code for Print Reports Using Crystal Report.
But Now i am using SQL CE.. here i don't know how to write code for print using SQL CE. please help me...

Thank you


public void printreport(string Reportname, int Billnumber, string SetupFileName)
{
try
{
timer1.Stop();
ReportDocument rptDoc = new ReportDocument();
rptDoc.Load(Application.StartupPath + "\\Reports\\" + Reportname);
//rptDoc.Load(Application.StartupPath+ "\\Reports\\");

rptDoc.SetDatabaseLogon(Globvar.Database, Globvar.DBPassword);
foreach (CrystalDecisions.CrystalReports.Engine.Table CurrTable in rptDoc.Database.Tables)
{
TableLogOnInfo tabloginfo = CurrTable.LogOnInfo;
tabloginfo.ConnectionInfo.ServerName = Globvar.DBServerName;
tabloginfo.ConnectionInfo.DatabaseName = Globvar.Database;
tabloginfo.ConnectionInfo.UserID = Globvar.DBUserName;
tabloginfo.ConnectionInfo.Password = Globvar.DBPassword;
CurrTable.ApplyLogOnInfo(tabloginfo);
break;
}
rptDoc.SetParameterValue("BllNo", Billnumber);
if(File.Exists(Application.StartupPath+"\\"+SetupFileName))

{
string[] x =new string[3];
StreamReader Srdr=new StreamReader(Application.StartupPath+"\\"+SetupFileName);
try
{
x[0]=Srdr.ReadLine();
x[1]=Srdr.ReadLine();
x[2]=Srdr.ReadLine();

rptDoc.PrintOptions.PrinterName=x[2].ToString();
rptDoc.PrintToPrinter(1, false, 0, 0);

Srdr.Close();
}
catch(Exception e)
{
MessageBox.Show(e.ToString());
Srdr.Close();
}

}


}
catch (Exception ee)
{
MessageBox.Show(ee.ToString());
}
timer1.Start();
}

ErikEJ said...

Prashant: Please ask in the MSDN forum. I know nothng about Crystal Reports

Deepak said...

Hello Erik,

In my application(native) that is using OLEDB to connect to a SQL Server Compact 3.5 database, My application is not able to locate the definitions of the following types and is hence failing to compile:
DB_UPARAMS, DBCOUNTITEM, DBBYTEOFFSET, DBROWCOUNT.
I have the 3 header files included (sqlce_sync.h, sqlce_err.h, and sqlce_oledb.h) and added the reference to the libraries (ole32.lib, oleaut32.lib, uuid.lib).
What could be wrong?
Thanks

ErikEJ said...

Deepak: I am not a C++ expert, suggest you ask in the MSDN forum

bhanu said...

give me step by step explain

Steve Owen said...

Hi Erik. Thanks for a great utility. I wonder if you could add a switch to the command line of Export2SqlCE so that it didn't split the output into multiple files. Secondary sqlce files usually fail with "The column cannot be modified", but if I combine the sqlce files into one big one, it works. It looks like it's missing SET IDENTITY_INSERT commands on subsequent output files. Otherwise it's an invaluable too. Thanks so much. Just looking to be able to fully automate it.

ErikEJ said...

Hi Steve: I will log this error, multiple files are there to avoid OutOfMemory errors, but of course "SET IDENTITY INSERT" must be kept if possible.

ErikEJ said...

Hi Steve, I have just released version 3.5.1.7, could you let me know if that works for you?

Rohith_JoY said...

IS export2sqlce compatible with SQL server 2005 express..?

ErikEJ said...

Rohit: Yes.

Adithya said...

Hi ErikEJ,
From where i can get the Export2SQLCE.exe?
I found this only exportsqlce.

ErikEJ said...

http://exportsqlce.codeplex.com/releases/view/52878#DownloadId=160019

Adithya said...

Thank you..

Anonymous said...

Erik,

I have been searching high and low for a tool to convert our DB to a SQLCE DB and this did the trick! Just wanted to say thanks :)

- a very appreciative intern

Cris J. said...

Hi

What happens with store procedures and views ?
Do it something with them?

Thanks!

ErikEJ said...

As SQL Compact does not support views and stored procedures, ntihing happens with them.

Moses said...

I got this error when generating SqlCe compatible script from my sql server 2008

C:\SqlCE>Export2SQLCE.exe "Data Source=MOSES-ICMA;Initial Catalog=Storekeeping;Integrated Security=True" Storekeeping.sql schemaonly
Error: System.TypeInitializationException: The type initializer for 'System.Data.SqlClient.SqlConnection' threw an exception. ---> System.TypeInitializationException: The type initializer for 'System.Data.SqlClient.SqlConnectionFactory' threw an exception. ---> System.TypeInitializationException: The type initializer for 'System.Data.SqlClient.SqlPerformanceCounters' threw an exception. ---> System.ArgumentNullException: Value cannot be null.
Parameter name: name
at System.Resources.ResourceManager.GetString(String name, CultureInfo culture)
at System.Diagnostics.MonitoringDescriptionAttribute.get_Description()
at System.Data.ProviderBase.DbConnectionPoolCounters.Counter..ctor(String categoryName, String instanceName, String counterName, PerformanceCounterType counterType)
at System.Data.ProviderBase.DbConnectionPoolCounters..ctor(String categoryName, String categoryHelp)
at System.Data.SqlClient.SqlPerformanceCounters..ctor()
at System.Data.SqlClient.SqlPerformanceCounters..cctor()
--- End of inner exception stack trace ---
at System.Data.SqlClient.SqlConnectionFactory..cctor()
--- End of inner exception stack trace ---
at System.Data.SqlClient.SqlConnection..cctor()
--- End of inner exception stack trace ---
at System.Data.SqlClient.SqlConnection..ctor()
at System.Data.SqlClient.SqlConnection..ctor(String connectionString)
at ErikEJ.SqlCeScripting.ServerDBRepository..ctor(String connectionString, Boolean keepSchemaName)
at ExportSqlCE.Program.Main(String[] args)

ErikEJ said...

Moses: I could mean that your .NET machine.config is corrupted - try from another machine to verify, and repair the .NET installation.

chinuexpert said...

Hello ErikEJ,
I executed the export2sqlce command, it executed successfully after this there are many script files are created. so how can i executed those all files at a time? Please get back to me as soon as possible, i need this very urgently. please.

ErikEJ said...

The batch file does that for you...

chinuexpert said...

Ya this is right. But i want to make sdf file of that. So for thos what can i do? Pleaae.

ErikEJ said...

Just run the batch file, it will create the sdf

chinuexpert said...

Ya it create a sdf file. I given the relationship to database but it does not showing in sdf file. Why is it so?

ErikEJ said...

I do not know what you mean, please log an issue with more details on exportsqlce.codeplex.com

chinuexpert said...

I mean to say after running batch file it created sdf file, but i set the db relations in sql server those relations are not showing in sdf database. For this problem wht can i do to solve this?

ErikEJ said...

Please post an issue on the codeplex issue tracker with additional information (scripts etc.)

Unknown said...

Hi Eric, you could assist me, I have my program running in sql server and sql server ce migrate to visual studio 8, do not send me any error, simply does not open the base and I can not read the records, any suggestions that I can be doing wrong. thanks

ErikEJ said...

Fer: I would be happy to help, but I do not understand your issue, maybe email me or try to share more information in the MSDN forum (not here, please)

Unknown said...

Hi Erik

What does this line do in the batch file ? I get a file not found error.

sqlcecmd40 -d "Data Source=test.sdf" -i test.sqlce > log.txt

ErikEJ said...
This comment has been removed by the author.
ErikEJ said...

Matthew: suggest yiu look at the sqlcecmd codeplex site for documentation