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
This will create a file named C:\aw.sqlce – let’s have a look:
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:
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
Try to re-install SSMS 2008 - could you share the exact error messsage?
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].
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.
Is it possible to perform the reverse, I mean from sql ce 4 to sql server (express or standard)?
Thanks
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
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
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.
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)
You need to install the x64 SQL Server Compact 3.5 dektop runtime on your computer.
Completely right thank's a lot :D
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..
Prashant: Suggest you ask in the MSDN Forum and provide more details/a code sample of what you trying to achieve.
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....
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).
Thank You Erik ..
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....
Parshant: Suggest you ask in the MSDN Compact Framework forum
hello
How to sync data from SQL SERVER 2000 to SQL CE 3.5 and vice versa.....
thank you
Prashant: That is not supported by any MS technology I know of, upgrade your SQL Server.
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....
Prashant: Please ask in the MSDN Smart Device Dev forum.
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();
}
Prashant: Please ask in the MSDN forum. I know nothng about Crystal Reports
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
Deepak: I am not a C++ expert, suggest you ask in the MSDN forum
give me step by step explain
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.
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.
Hi Steve, I have just released version 3.5.1.7, could you let me know if that works for you?
IS export2sqlce compatible with SQL server 2005 express..?
Rohit: Yes.
Hi ErikEJ,
From where i can get the Export2SQLCE.exe?
I found this only exportsqlce.
http://exportsqlce.codeplex.com/releases/view/52878#DownloadId=160019
Thank you..
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
Hi
What happens with store procedures and views ?
Do it something with them?
Thanks!
As SQL Compact does not support views and stored procedures, ntihing happens with them.
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)
Moses: I could mean that your .NET machine.config is corrupted - try from another machine to verify, and repair the .NET installation.
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.
The batch file does that for you...
Ya this is right. But i want to make sdf file of that. So for thos what can i do? Pleaae.
Just run the batch file, it will create the sdf
Ya it create a sdf file. I given the relationship to database but it does not showing in sdf file. Why is it so?
I do not know what you mean, please log an issue with more details on exportsqlce.codeplex.com
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?
Please post an issue on the codeplex issue tracker with additional information (scripts etc.)
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
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)
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
Matthew: suggest yiu look at the sqlcecmd codeplex site for documentation
Post a Comment