Using SQL Server CE

IWS projects running in EmbeddedView or CEView can interface with Microsoft SQL Server CE (SQL CE) databases on the same device by using the OLEDB Provider for SQL Server CE. This provider must be installed on your Windows Embedded device.

Because the provider is only installed on the device and not on your development workstation, you cannot select it in the Data Link Properties dialog when you develop your project. Instead, you must manually enter the connection string using the following format:

Version	Connection String
SQL CE 2.0	`Provider=SQLCE; Data Source=database_path`
SQL CE 3.0	`Provider=SQL_CE3.0; Data Source=database_path`
SQL CE 3.5	`Provider=SQL_CE3.5; Data Source=database_path`

Note: The database interface was fully tested with these versions of SQL CE. However, other versions might also be supported; please contact Customer Support for more information.

Examples:

Access a database file at the fixed location HarddiskMyDatabase.sdf:
```
  Provider=SQLCE; Data Source=HarddiskMyDatabase.sdf  
```
The exact path depends on how your device’s non-volatile memory is organized. Please consult the manufacturer’s documentation.
Access a database file at the location specified by the String tag DatabaseFile:
```
  Provider=SQLCE; Data Source={DatabaseFile}  
```
The curly brackets (e.g., {tagname}) indicate that it is an indirect tag.

Please keep in mind that SQL CE is only intended for lightweight databases and simple transactions. It cannot efficiently handle large amounts of data, such as the Alarm and Event histories. In those cases, we recommend that you either use the Proprietary history format or use a more powerful relational database like the full version of Microsoft SQL Server. If you must use SQL CE, then we recommend that you use it only as a Secondary database with the Store and Forward option selected. For more information, see Saving your alarm history / event log to an external database.

CAUTION:

When using a Database worksheet or the DB/ERP functions to access a SQL CE database, remember…

Column/field names must match exactly or the database commands will fail; and
IWS data types will be converted into possibly unexpected SQL data types. The following table shows how they’re converted:

IWS SQL CE
Boolean int
Integer int
Real real
String nvarchar

Web Studio Help

Using SQL Server CE

Using SQL Server CE

Web Studio Help > Using SQL Server CE

Web Studio Help

IWS	SQL CE
Boolean	`int`
Integer	`int`
Real	`real`
String	`nvarchar`