Data Types and RDA

The following tables show the supported data types and data type mappings, and the unsupported data types that you can use with remote data access (RDA) to access data in a Microsoft SQL Server database.

Supported Data Types

SQL Server data type SQL Server Compact Edition data type

bigint (int 8)

bigint

binary (n)

binary (n)

bit

bit

Character (n)(synonym:char (n))

nationalcharacter (n) or ntext

If the length of the data is 4000 characters or less, RDA maps the SQL Server character data to SQL Server 2005 Compact Edition (SQL Server Compact Edition) nationalcharacter. Otherwise, it maps it to SQL Server Compact Edition ntext.

If the length of the ntext data exceeds the length of the character column, push fails when the data is sent from SQL Server Compact Edition to SQL Server.

character varying (n|max) (synonyms:char varyingvarchar (n|max))

national character varying (n) or ntext

If the length of the data is 4000 characters or less, RDA maps the SQL Server character varying data to SQL Server Compact Edition national character varying; otherwise, it maps it to SQL Server Compact Edition ntext.

If the length of the character varying (max) data exceeds the length of the ntext column, push fails when the data is sent from SQL Server to SQL Server Compact Edition.

datetime

datetime

decimal

Not Supported. Use numeric.

double precision

double precision

float

float

image

image

integer(synonym:int)

integer

money

money

national character (n)(synonyms: nchar (n))

national character (n)

national character varying (n)

national character varying (n)

national character varying (max) (synonyms: nvarchar(max))

ntext

If the length of the national character varying (max) data exceeds the length of the ntext column, push fails when the data is sent from SQL Server to SQL Server Compact Edition.

ntext

ntext

numeric(synonyms:decimal, dec)

numeric

real

real

smalldatetime

datetime

If the precision of the datetime data exceeds the precision of the smalldatetime column, synchronization fails when the data is sent from SQL Server Compact Edition to SQL Server.

smallint (int 2)

smallint (int 2)

smallmoney

money

If the precision of the money data exceeds the precision of the smallmoney column, synchronization fails when the data is sent from SQL Server Compact Edition to SQL Server.

sql_variant

Not supported for RDA

text

ntext

If the length of the text data exceeds 1,073,741,823 characters, synchronization fails when data is sent from SQL Server to SQL Server Compact Edition.

tinyint

tinyint

uniqueidentifier

uniqueidentifier

varbinary (n)

varbinary (n)

varbinary (max)

image

If the length of the varbinary (max) data exceeds the length of the image column, synchronization fails when the data is sent from SQL Server to SQL Server Compact Edition.

XML

ntext

Unsupported Data Types

Data Type Explanation

sql_variant, DATE, TIME, UTCDATETIME, FILESTREAM, UDT

Not supported

Do not include columns of this data type in the RDA Pull method.

Using Data Types

When possible, choose data types that are supported by both SQL Server and SQL Server Compact Edition. As a result, RDA does not have to perform data mapping. When this is not possible, make sure your application validates the values stored in SQL Server Compact Edition to ensure that RDA can map these values between SQL Server and SQL Server Compact Edition.

You cannot pull data from a table having a primary key of type char or varchar with length greater than 4000 characters. These column types with character lengths greater than 4000 are mapped to ntext, and a primary key cannot be created on an ntext column.

Once a data type is mapped to ntext in SQL Server Compact Edition subscription database, it will remain ntext even if the type is changed in SQL Server, until the subscription is reinitialized. Also, if a data type is mapped to ntext in SQL Server Compact Edition, any indexes on the SQL Server data type, such as nvarchar (MAX), are ignored and not created in SQL Server Compact Edition.

See Also

Concepts

Data Types and Replication

Other Resources

Data Type Mappings (SQL Server Compact Edition)

Help and Information

Getting SQL Server Compact Edition Assistance