Saturday, August 21, 2010

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:

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!

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. (Exception 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 specified.File name: 'System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' at SqlCeCmd.Program.Main(String[] args)

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

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.exe "Data Source=MIRAK\SQLEXPRESS;Initial Catalog=MiniMobileDB;Integrated Security=True" MiniMobileDB.sqlceGenerating 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

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."

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).

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..?

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

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.

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.

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