CREATE DATABASE (SQL Server Compact Edition)
Creates a new database and the file used to store the database.
The name of the new database. The database_name argument is a file name and is restricted by the naming and size limitations of the operating system. Any valid character that can be used in a file name can be used for the database name. If a path name is not specified, the database is created in the current directory. By default, the file name extension for a database name in Microsoft SQL Server 2005 Compact Edition (SQL Server Compact Edition) is .sdf.
SQL Server Compact Edition returns an error if a user connects to a password-protected database without a password. The database_password must be enclosed in single quotation marks.
If the optional ENCRYPTION ON keyword is used, the database is encrypted with 128-bit encryption. You must specify a password to use encryption.
Specifies the default collation for the database. The collation_name argument can only be a Windows collation. If collation_name is not specified, the database is assigned the default collation of the device. For a list of collation names, see COLLATE (SQL Server Compact Edition).
Specifies the comparison style of characters. SQL Server Compact Edition supports only CI_AS (case insensitive and accent sensitive). The collation_name and comparison_style parameters must not be enclosed in single or double quotation marks.
To execute the CREATE DATABASE statement, you must be connected to a database. For information about connecting to a database, see SQL Server Compact Edition Query Analyzer.
A. Creating a password-protected database
The following example creates a database with a password.
CREATE DATABASE "\test1.SDF" DATABASEPASSWORD '<tst1_pwd>'
When a user tries to connect to a password-protected database without supplying the correct password, SQL Server Compact Edition returns this error:
Error: 0x80040e4d DB_SEC_E_AUTH_FAILED Minor Error: (25028) Description: Authentication failed. Interface defining error: IID_IDBInitialize Param. 0:0 Param. 1:0 Param. 2:0 Param. 3: Param. 4: Param. 5:
For information about errors, see SQL Server Compact Edition Errors.
B. Creating a database with a collation specified
The following example specifies a specific collation for the database being created.
CREATE DATABASE "SpanishDB.sdf" DATABASEPASSWORD '<MyPwd>' COLLATE Traditional_Spanish_CI_AS