Home

Documentation for SqlCeCmd utility

The sqlcecmd utility lets you enter SQL Compact Transact-SQL statements, and use script files at the command prompt or in a Windows script file. This utility uses the SQL Compact ADO.NET provider to execute Transact-SQL batches. Requires .NET Framework 2.0 and the SQL Compact runtime.

Syntax

SqlCeCmd 1.2.0.17
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|verify|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

Command-line Options

-d connection_string

The SQL Compact connection string to be used. For connection string syntax, see http://msdn.microsoft.com/en-us/library/system.data.sqlserverce.sqlceconnection.connectionstring.aspx. This parameter is required.

-e engine_command

Runs the specified SqlCeEngine command against the database: Create, Upgrade, Shrink, Compact, Repair. Cannot be used with the –z, -i or -q options.

-z database options

Changes the database options. Specify connection string for new database options. Cannot be used with the –q, -i or -e options. The following tokens are valid:

• Password
• LCID
• Encryption mode
• Case Sensitive

All other SqlCeConnection.ConnectionString tokens are ignored

Sample usage:

sqlcecmd –d "Data Source=C:\mydb.sdf" -z "Data Source=;Password=secret123;Case sensitive=true;"

Query Execution Options

-q " cmdline query "

Executes a single query. Cannot be used with the –z, -i or -e options. Use quotation marks around the query, as shown in the following example. At the command prompt, type:

sqlcecmd -d "Data Source=c:\mydb.sdf" -q "SELECT FirstName, LastName FROM Contact WHERE LastName LIKE 'Whi%'"

sqlcecmd -d "Data Source=c:\mydb.sdf" -q "SELECT TOP 5 FirstName FROM Contact"

Input/Output Options

-i input_file

Identifies the file that contains a batch of SQL statements. Each statement in the file must be separated by the word "GO" on a line by itself. sqlcecmd will first check to see whether the specified file exists. If the file does not exist, sqlcecmd will exit. Cannot be used with the –z, -e or -q options. Path examples: -i C:<filename> -i \<Share$><filename> -i "C:\Some Folder<file name>" File paths that contain spaces must be enclosed in quotation marks.

-o output_file

Identifies the file that receives output from sqlcecmd. The output_file is always stored in Unicode format. If the file name is not valid, an error message is generated, and sqlcecmd exits. This file will be created if it does not exist. A file of the same name from a prior sqlcecmd session will be overwritten. Path examples: -o C:< filename> -o \<Share$><filename> -o "C:\Some Folder<file name>" File paths that contain spaces must be enclosed in quotation marks.

-v display database information

Displays the following database information as the first output: Engine version, locale, encryption mode, case sensitivity, database size, creation date/time

-R use client regional settings

Set the utility to use client regional settings when it converts currency, and date and time data to character data. The default is InvariantCulture settings.

Formatting Options

-n hide query output

Quiet mode - hides any output from query results

-h headers

Specifies the number of rows to print between the column headings. The default is to print headings one time for each set of query results. Use 0 to specify that headers must not be printed. Any value that is not valid causes sqlcecmd to generate an error message and then exit.

-s col_separator

Specifies the column-separator character. The default is a blank space. To use characters that have special meaning to the operating system such as the ampersand (&), or semicolon (;), enclose the character in quotation marks ("). The column separator can be any 8-bit character.

-W remove trailing spaces

This option removes trailing spaces from a column. Use this option together with the -s option when preparing data that is to be exported to another application. Cannot be used with the -y or -Y options.

-x write SELECTs as XML

This option writes output from SELECTs as XML. This is useful in order to preserve the full contents of long columns, including nvarchar, nchar, image and text. Miscellaneous Options

-? show syntax summary

Displays the syntax summary of sqlcecmd options.

Remarks

Options do not have to be used in the order shown in the syntax section. The total length of the sqlcecmd command line in the command environment (Cmd.exe), including all arguments and expanded variables, is that which is determined by the operating system