Export (0) Print
Expand All

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.

SQL Server data type

SQL Server Compact 3.5 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 Compact national character. Otherwise, it maps it to SQL Server Compact 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 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 national character varying; otherwise, it maps it to SQL Server Compact 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.

date

nchar(10) value of the form 'YYYY-MM-DD'

SQL Server Compact stores wide characters. Conversion support for this type was provided by the SQL Server Compact SP1 release.

datetime

datetime

datetime2

nvarchar(27) value of the form 'YYYY-MM-DD hh:mm:ss.nnnnnnn'

SQL Server Compact stores wide characters. Conversion support for this type was provided by the SQL Server Compact 3.5 SP1 release.

datetimeoffset

nvarchar(34) value of the form 'YYYY-MM-DD hh:mm:ss.nnnnnnn [+/-] hh:mm'

SQL Server Compact stores wide characters. Conversion support for this type was provided by the SQL Server Compact 3.5 SP1 release.

decimal

Not Supported. Use numeric.

double precision

double precision

float

float

filestream

image

Conversion support for this type was provided by the SQL Server Compact 3.5 SP1 release.

geography

image

Conversion support for this type was provided by the SQL Server Compact 3.5 SP1 release.

geometry

image

Conversion support for this type was provided by the SQL Server Compact 3.5 SP1 release.

hierarchyid

image

Conversion support for this type was provided by the SQL Server Compact 3.5 SP1 release.

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.

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 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 to SQL Server.

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.

time

nvarchar(16) value of the form 'hh:mm:ss.nnnnnnn'

SQL Server Compact stores wide characters. Conversion support for this type was provided by the SQL Server Compact 3.5 SP1 release.

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.

XML

ntext

Data Type

Explanation

sql_variant, UTCDATETIME, UDT

Not supported

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

When possible, choose data types that are supported by both SQL Server and SQL Server Compact. 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 to ensure that RDA can map these values between SQL Server and SQL Server Compact.

Existing applications that do not natively support date, datetime2, datetimeoffset, and time need to deal with data that is mapped to compatible data types like nchar and nvarchar.

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 the SQL Server Compact 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, any indexes on the SQL Server data type, such as nvarchar (MAX), are ignored and not created in SQL Server Compact.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft