Wednesday, April 8, 2009

SqlCeCmd tutorial part one – Managing database files

This tutorial will show various samples of using SqlCeCmd, which is available at http://sqlcecmd.codeplex.com – sqlcecmd is the “equivalent” of sqlcmd for the full SQL Server product.

The series will be in three parts, the first one demonstrates how to manage entire database files, the second how to create database objects and insert/update data, the third relating to querying data.

To get information about usage

Simply type “sqlcecmd” at the command prompt:

image

Notice that the –d parameter is always required. It is a SQL Compact connection string as documented here: http://msdn.microsoft.com/en-us/library/system.data.sqlserverce.sqlceconnection.connectionstring.aspx

To create a new database file with default settings

sqlcecmd –d “Data Source=C:\test.sdf” –e create

To create a new database file with password and case sensitivity

sqlcecmd
-d "Data Source=C:\test.sdf;Case Sensitive=True;Password=p"
-e create

To upgrade a version 3.0/3.1 database file to 3.5 format

sqlcecmd -d "Data Source=C:\Northwind.sdf" -e upgrade

To compact a database file

sqlcecmd -d "Data Source=C:\Northwind.sdf" -e compact
Similarly you can shrink or repair a database file, with the shrink, repairdelete and repairrecover options.

To change database options

You can change the following four database options on an  existing database: Change password, Change encryption mode, Change locale id, Change case sensitivity

Possible values for encryption mode are:
engine default or platform default or  ppc2003 compatibility

sqlcecmd
-d "Data Source=C:\test.sdf;Case Sensitive=True;Password=p"
-z "Data Source=;Encryption Mode=ppc2003 compatibility;Case Sensitive=false"

To drop a database

del c:\test.sdf

:-)

Stay tune for the next part, creating and modifying database objects.

25 comments:

Schaff Blog said...

I love your drop db statement. That is some serious SQL. :)

Chunsong said...

Hi,
When I double click the SqlCeCmd.exe, a flash command prompt window, then disappeared. How to figure out this problem, do I need setting some params?

ErikEJ said...

Alex: You must start a Command Prompt first, and then type "sqlcecmd" in the command prompt. You start a Command Prompt by pressing Windows key + R, and the type "cmd".

Unknown said...

Hi,
no matter what I write as parameters, I always see the default (welcome) screen. I'm using Vista and sqlcecmd version 1.2. I tried with elevation and without. Same default screen, no matter what I type after.
Am I the only one?
Thanks!

ErikEJ said...

Could you provide a sample of what you are typing? - Remember to run from a command prompt!

Unknown said...

Of course I run it from command prompt (if I see the default screen). Actually I need it for some pre-build events in Visual Studio.
Here is a paste from my cmd.


D:\temp\SqlCeCmd.1.2>sqlcecmd -d "Data Source=C:\test.sdf" -e create
SqlCeCmd 1.2.0.0
Copyright (C) 2009 Erik Ejlskov Jensen
Contact me at my blog: http://erikej.blogspot.com
Check for updates at: http://sqlcecmd.codeplex.com

d Required. SQL Compact connection string

e Run SQL Compact engine actions:
shrink|compact|create|upgrade|repairdelete|repairrecover
z Change database options:
Password,Encryption Mode,Locale Id,Case Sensitivity
q Command line query
i SQL query input file

o Output file
v Display database information
R Use client regional settings
h Headers - 0 to Int32.MaxValue
s Column separator
W Remove trailing spaces
x Output SELECTs as XML
? Display this help screen

D:\temp\SqlCeCmd.1.2>

ErikEJ said...

I cannot repro on Win7 - which OS language are you running - suggest we continue this discussion here: http://sqlcecmd.codeplex.com/WorkItem/List.aspx

Unknown said...

I mentioned it above, I'm using Vista Ultimate 32bit SP2. I opened an work item with details.

Unknown said...

This is a very good utility for sql server compact development. However, when I try to use a script file with sp_addtype and got the error parsing the query. My script file is used to create some tables in the database. The sp_addtype is used to define some common data types for the table. Please help.

ErikEJ said...

Kevin: SQL Server Compact does not support alias data types, you must use the base type - so it also does not support sp_addtype.

DaveS said...

Does SqlCeCmd work when the database is residing on a PPC, connected via ActiveSync?

I tried using a filename such as "Data Source=Mobile Device\Program Files\test\db\test.sdf", but the path is not valid.

ErikEJ said...

Dave: Currently it does not support Mobile Device connections - you can copy the database form your device to your PC, and work with it there. Would it be useful to work with the sdf file directly on the mobile device?

BERTHOLD EDMUND said...

what about exporting dats as a sql file?

ErikEJ said...

Berthold: Use my ExportSqlCe utility: http://exportsqlce.codeplex.com

Vrushalie said...

Hi .. i am trying to use this tool to create database file and insert some data.
sqlcecmd –d “Data Source=C:\test.sdf” –e create this command doesn't work. I m getting error as

SqlCeCmd 1.2.0.6
Copyright (C) 2010 Erik Ejlskov Jensen
Contact me at my blog: http://erikej.blogspot.com
Check for updates at: http://sqlcecmd.codeplex.com

Sample usage:
sqlcecmd -d "Data Source=C:\nw.sdf" -q "SELECT * FROM Shippers"

d Required. SQL Compact ADO.NET connection string

e Run SQL Compact engine actions:
shrink|compact|create|upgrade|repairdelete|repairrecover
z Change database options:
Password,Encryption Mode,Locale Id,Case Sensitivity
q Command line query
i SQL query input file

n Hide query output

o Output file
v Display database information
R Use client regional settings
h Headers - 0 to Int32.MaxValue
s Column separator
W Remove trailing spaces
x Output SELECTs as XML
? Display this help screen

could you please help me with that. Or could you please suggest me some better option. thanks in advance

ErikEJ said...

Make sure to type normal quotes ("), that is probably the issue

Vrushalie said...

hey Erike thanks :) also could you tell me how to delete data from any table?

ErikEJ said...

Vrushaile: "DELETE FROM Mytable where x = y"

Vrushalie said...

Thanks Erik :) :) it was really helpfull...

Wireless Task said...

I beleive sql compact can only take 4 gb for mobile and desktop program. what if the person who uses the program reaches 4gb? his program will be then of no use? what is the solution for this problem.

what about adding a backup the database and also a delete option to empty the database? and then start fresh and if they want to see old data, then just restore a backup and check? is this a good option?

Silas Mendes said...

Cool :) Thanks!

The Mad Ape said...

Just want to say that this is a fantastic utility. Once I read through everything and did a couple of tests I set your beast loose on my databases.

Worked flawlessly to get all of my dbs up to 4.0

Thank you for your service to the community!

SunilC said...

HI Eric,

I am using your application SqlCE.exe .
I have given following command in command promp
D:\>SqlCeCmd.exe -d "Data Source=D:\FirmAndDP\QuantGUI\QuantGUI\QuantDBSG.sdf" -
q "select * from userlogin"

but getting following error

Inner Exception: System.DllNotFoundException: Unable to load DLL 'sqlceme35.dll'
: The specified module could not be found. (Exception from HRESULT: 0x8007007E)
at System.Data.SqlServerCe.NativeMethods.GetSqlCeVersionInfo(IntPtr& pwszVers
ion)
at System.Data.SqlServerCe.NativeMethods.LoadValidLibrary(String modulePath,
Int32 moduleVersion)
at System.Data.SqlServerCe.NativeMethods.LoadNativeBinaries()

Please let me know from where I can download above mention DLL.

Thanks,
Sunil

ErikEJ said...

You need to install the SQL Server Compact 3.5 SP2 runtime MSI

SunilC said...

Thanks Eric,

Its working now.

Regards,
Sunil