Provider-Specific Properties (OLE DB)

SQL Server Compact 4.0 defines a several provider-specific properties in the DBPROPSET_SSCE_DBINIT and DBPROPSET_SSCE_SESSION property sets.

DBPROPSET_SSCE_DBINIT Property Set

The DBPROPSET_SSCE_DBINIT property set contains the following properties that are specific to the OLE DB provider for SQL Server Compact 4.0.

Property ID

Description

DBPROP_SSCE_DBPASSWORD

Type: VT_BSTR

Typical R/W: R/W

Description: SQL Server Compact 4.0 OLEDB:Database Password

Notes: The password used to open the database. The default is no password.

DBPROP_SSCE_DBCASESENSITIVE

Type: VT_BOOL

Typical R/W: R/W

Description: SQL Server Compact 4.0 OLEDB:Case Sensitive 

Notes: Determines whether or not the database collation is case sensitive. Must be set to true to enable case-sensitive collation or false for case-insensitive collation. If not specified, the default value is false.

NoteNote
This property is introduced starting with the SQL Server Compact 3.5 SP1 release. For more information, see Working with Collations (SQL Server Compact).

DBPROP_SSCE_TEMPFILE_DIRECTORY

Type: VT_BSTR

Typical R/W: R/W

Description: SQL Server Compact 4.0 OLEDB: Temp File Directory

Notes: Used to specify the temp file directory. If no temp file directory is specified (the default), the main database is used as the temp database.

DBPROP_SSCE_TEMPFILE_MAX_SIZE

The maximum size of the temporary database file in MB. The default value is 128 MB.

DBPROP_SSCE_DEFAULT_LOCK_ESCALATION

The number of locks the engine will acquire before attempting escalation from row to table or from page to table. The default value is 100 locks.

DBPROP_SSCE_AUTO_SHRINK_THRESHOLD

The percent of free space in the file before autoshrink will start. A value of 100 percent disables autoshrink. The default value is 60 percent.

DBPROP_SSCE_MAX_DATABASE_SIZE

The maximum size of the database in MB. The default value is 128 MB.

DBPROP_SSCE_FLUSH_INTERVAL

The interval at which committed transactions are flushed to disk, in seconds. When a transaction is committed, it will be flushed to disk later, by default. The flush interval indicates that these transactions should be flushed every n seconds where n is the flush interval. This parameter is ignored if the database is already open and another client has already specified this parameter. The store is always internally flushed when closed. The default value for the flush interval is 10 seconds.

DBPROP_SSCE_DEFAULT_LOCK_TIMEOUT

The default number of milliseconds a transaction will wait for a lock. This property can be overridden for an individual transaction using the DBPROP_SSCE_LOCK_TIMEOUT property. The default value is 5000 milliseconds for desktops and 2000 milliseconds for devices.

DBPROP_SSCE_ENCRYPTIONMODE

Specifies the encryption that can be used if the database is password protected. There are two encryption modes that are supported on all SQL Server Compact 4.0 supported platforms and are as follows:

Platform Default. This option assigns the default encryption for the platform that was used to create the SQL Server Compact 4.0 database. For example, a database created on Windows XP with Platform Default selected will use encryption for Windows XP. If an encrypted database was created (and has this option selected) on any one of the following platforms, you will not be able to open it on either Windows CE 4.2.

  • Windows CE 5.0

  • Windows CE 6.0

  • Windows XP

  • Windows Server 2003 

  • Windows Vista 

Engine Default. This option provides encryption that works on most SQL Server Compact-supported platforms (but not on Windows CE 4.2.

  • Windows CE 5.0

  • Windows CE 6.0

  • Windows XP

  • Windows Server 2003 

  • Windows Vista 

DBPROP_SSCE_MAXBUFFERSIZE

The largest amount of memory, in kilobytes, that SQL Server Compact 4.0 can use before it starts flushing changes to disk. The default value is 640 kilobytes.

DBPROPSET_SSCE_SESSION Property Set

The DBPROPSET_SSCE_SESSION property set contains the following property that is specific to the OLE DB provider for SQL Server Compact 4.0.

Property ID

Description

DBPROP_SSCE_LOCK_TIMEOUT

Number of milliseconds a transaction will wait for a lock. The default value is 2000 milliseconds.

DBPROP_SSCE_LOCK_ESCALATION

Number of locks the engine will acquire before trying escalation from row to page or from page to table. The default value is 100 locks.

DBPROP_SSCE_TRANSACTION_COMMIT_MODE

  • Type: VT_I4

  • Typical R/W: R/W

  • Description: SQL Server Compact 4.0

  • Notes: Specifies whether the engine should flush the buffer pool after commit. Must be one of the values from below:

    • DBPROPVAL_SSCE_TCM_DEFAULT (Asynchronous commit to disk)

    • DBPROPVAL_SSCE_TCM_FLUSH (Synchronous commit to disk)

The default value is DBPROPVAL_SSCE_TCM_DEFAULT (deferred flush).

DBPROPSET_SSCE_COLUMN Property Set

The DBPROPSET_SSCE_COLUMN property set contains the following properties that are specific to the OLE DB provider for SQL Server Compact 4.0.

Property ID

Description

DBPROP_SSCE_COL_ROWGUID

  • Type: VT_BOOL

  • Typical R/W: R/W

  • Description: SQL Server Compact 4.0 

  • OLE DB: Row GUID Column

  • Notes: Specifies a column as a row GUID column. Only one GUID column can be the row's GUID column. The default value is false (no column is specified).

DBPROPSET_SSCE_ROWSET Property Set

The DBPROPSET_SSCE_ROWSET property set contains the following property that is specific to the OLE DB provider for SQL Server Compact 4.0.

Property ID

Description

DBPROP_SSCE_LOCK_HINT

  • Type: VT_I4

  • Typical R/W: R/W

  • Description: SQL Server Compact 4.0 

  • Notes: Indicates the level of locking performed by the rowset. Must be a valid combination of one or more of the following 8 values:

    • DBPROPVAL_SSCE_LH_HOLDLOCK

    • DBPROPVAL_SSCE_LH_TABLOCK

    • DBPROPVAL_SSCE_LH_NOLOCK

    • DBPROPVAL_SSCE_LH_DBLOCK

    • DBPROPVAL_SSCE_LH_ROWLOCK

    • DBPROPVAL_SSCE_LH_UPDLOCK

    • DBPROPVAL_SSCE_LH_PAGLOCK

    • DBPROPVAL_SSCE_LH_XLOCK

The default value is DBPROPVAL_SSCE_LH_NOLOCK (no lock is used).