Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » MobiLink - Server Administration » MobiLink Data Mappings Between Remote and Consolidated Databases

Oracle data mapping Next Page

Microsoft SQL Server data mapping


Note

Only supported data types are included here.

SQL Anywhere or UltraLite remote date types mapped to Microsoft SQL Server consolidated data types

The following table identifies how SQL Anywhere or UltraLite remote data types are mapped to Microsoft SQL Server consolidated data types. For example, a column of type DATE on the remote database should be type DATETIME on the consolidated database.

SQL Anywhere or UltraLite data type

Microsoft SQL Server data type

Notes

CHAR(n<=8000)

VARCHAR(n)

CHAR(n>8000)

TEXT

LONG NVARCHAR

NTEXT

LONG VARCHAR

TEXT

NCHAR(n<=4000)

NVARCHAR(c)

NCHAR(n>4000)

NTEXT

NTEXT

NTEXT

NVARCHAR(n<=4000)

NVARCHAR(c)

NVARCHAR(n>4000)

NTEXT

TEXT

TEXT

UNIQUEIDENTIFIERSTR

UNIQUEIDENTIFIER

VARCHAR(n<=8000)

VARCHAR(c)

VARCHAR(n>8000)

TEXT

XML

XML or TEXT

For SQL Server 2005, use XML. For other versions, use TEXT.

UNSIGNED BIGINT

NUMERIC(20)

For download, values must be non-negative.

BIGINT

BIGINT

BIT

BIT

DECIMAL(p=<38,s)

DECIMAL(p,s)

SQL Server DECIMAL/NUMERIC precision ranges from 1 to 38, so p must be less than 39.

DECIMAL(p>38,s)

There is no corresponding data type in SQL Server.

DOUBLE

FLOAT(53)

FLOAT(p)

FLOAT(p)

UNSIGNED INTEGER

NUMERIC(11)

For download, values must be non-negative.

INTEGER

INT

NUMERIC(p=<38,s)

NUMERIC(p,s)

SQL Server DECIMAL/NUMERIC precision ranges from 1 to 38, so p must be less than 39.

NUMERIC(p>38,s)

There is no corresponding data type in SQL Server.

REAL

REAL

UNSIGNED SMALLINT

INT

For download, values must be non-negative.

SMALLINT

SMALLINT

UNSIGNED TINYINT

TINYINT

For download, values must be non-negative.

TINYINT

TINYINT

For download, values must be non-negative.

MONEY

MONEY

SMALLMONEY

SMALLMONEY

LONG VARBIT

TEXT

VARBIT(n<=8000)

VARCHAR(n)

VARBIT(n>8000)

TEXT

DATE

DATETIME

The year must be in the range 1753-9999.

DATETIME

DATETIME

SQL Server DATETIME values are accurate to 333.33 microseconds. The last digit of the fractional second is always rounded to one of 0, 3, or 7, so 0 and 1 round to 0; 2, 3, and 4 round to 3; 5, 6, 7, and 8 round to 7; and 9 rounds to 10. For download, SQL Anywhere keeps the original values from SQL Server, but for upload, the values may not be exactly the original values. Conflict resolution may fail. To successfully synchronize DATETIME, you can round the fractional second to 10 milliseconds. The year must be in the range 1753-9999.

SMALLDATETIME

SMALLDATETIME

SQL Anywhere and UltraLite SMALLDATETIME is implemented as TIMESTAMP. The Adaptive Server Enterprise SMALLDATETIME is accurate to the minute. 29.998 seconds or lower are rounded down to the nearest minute; values with 29.999 seconds or higher are rounded up to the nearest minute. SQL Anywhere or UltraLite SMALLDATETIME is accurate to the microsecond. In order to successfully synchronize, SQL Anywhere or UltraLite SMALLDATETIME must be rounded to the minute. The year must be in the range 1900-2078.

TIME

DATETIME

SQL Server TIME values are accurate to 333.33 microseconds. The last digit of the fractional second is always rounded to one of 0, 3, or 7, so 0 and 1 round to 0; 2, 3, and 4 round to 3; 5, 6, 7, and 8 round to 7; and 9 rounds to 10. For download, SQL Anywhere keeps the original values from SQL Server, but for upload, the values may not be exactly the original values. Conflict resolution may fail. To successfully synchronize TIME, you can round the fractional second to 10 milliseconds. The year must be in the range 1753-9999.

TIMESTAMP

DATETIME

SQL Server DATETIME values are accurate to 333.33 microseconds. The last digit of the fractional second is always rounded to one of 0, 3, or 7, so 0 and 1 round to 0; 2, 3, and 4 round to 3; 5, 6, 7, and 8 round to 7; and 9 rounds to 10. For download, SQL Anywhere keeps the original values from SQL Server, but for upload, the values may not be exactly the original values. Conflict resolution may fail. To successfully synchronize DATETIME, you can round the fractional second to 10 milliseconds. The year must be in the range 1753-9999.

BINARY(n<=8000)

VARBINARY(n)

BINARY(n>8000)

IMAGE

IMAGE

IMAGE

LONG BINARY

IMAGE

UNIQUEIDENTIFIER

UNIQUEIDENTIFIER

VARBINARY(n<=8000)

VARBINARY(n)

VARBINARY(n>8000)

IMAGE

Microsoft SQL Server consolidated data types mapped to SQL Anywhere or UltraLite remote data types

The following table identifies how Microsoft SQL Server consolidated data types are mapped to SQL Anywhere or UltraLite remote data types. For example, a column of type TEXT on the remote database should be type LONG VARCHAR on the consolidated database.

Microsoft SQL Server data typeSQL Anywhere or UltraLite data typeNotes

BIGINT

BIGINT

INT

INT

SMALLINT

SMALLINT

BIT

BIT

TINYINT

TINYINT

DECIMAL(p,s)

DECIMAL(p,s)

NUMERIC(p,s)

NUMERIC(p,s)

MONEY

MONEY

SMALLMONEY

SMALLMONEY

FLOAT(p)

FLOAT(p)

REAL

REAL

REAL can cause problems if the consolidated and remote databases don't allow the exact same (imprecise) values. We do not test all possible values, so care must be taken. To avoid problems, do not use these types as part of a primary key.

DATETIME

TIMESTAMP or DATETIME

The Adaptive Server Enterprise DATETIME values are accurate to 1/300 second. The last digit of the fractional second is always 0, 3, or 6. Other digits will be rounded to one of these three digits, so, 0 and 1 round to 0; 2, 3, and 4 round to 3; 5, 6, 7, and 8 round to 6; and 9 rounds to 10. For download, SQL Anywhere keeps the original values from Adaptive Server Enterprise, but for upload, the values may not be exactly the original values. If DATETIME is used for a primary key, conflict resolution may fail. In order to successfully synchronize DATETIME, you should round the fractional second to 10 milliseconds. The year must be in the range 1753-9999.

SMALLDATETIME

SMALLDATETIME

SQL Anywhere and UltraLite SMALLDATETIME is implemented as TIMESTAMP. The Adaptive Server Enterprise SMALLDATETIME is accurate to the minute. 29.998 seconds or lower are rounded down to the nearest minute; values with 29.999 seconds or higher are rounded up to the nearest minute. SQL Anywhere or UltraLite SMALLDATETIME is accurate to the microsecond. In order to successfully synchronize, SQL Anywhere or UltraLite SMALLDATETIME must be rounded to the minute. The year must be in the range 1900-2078.

CHAR(n)

VARCHAR(n)

There is no equivalence between SQL Anywhere CHAR and non-SQL Anywhere CHAR. SQL Anywhere CHAR is equivalent to VARCHAR. You should not use CHAR in a consolidated database column that is synchronized. If you must use non-SQL Anywhere CHAR, run the MobiLink server with the -b option.

VARCHAR(n)

VARCHAR(n)

TEXT

LONG VARCHAR

NCHAR(n)

NVARCHAR(c)

Not available in UltraLite.

There is no equivalence between SQL Anywhere NCHAR and non-SQL Anywhere NCHAR. SQL Anywhere NCHAR is equivalent to NVARCHAR. You should not use NCHAR in a consolidated database column that is synchronized. If you must use non-SQL Anywhere NCHAR, run the MobiLink server with the -b option.

NVARCHAR(c)

NVARCHAR(c)

Not available in UltraLite.

NTEXT

LONG NVARCHAR

Not available in UltraLite.

BINARY(n)

BINARY(n)

VARBINARY(n)

VARBINARY(n)

IMAGE

LONG BINARY

UNIQUEIDENTIFIER

UNIQUEIDENTIFIER

XML

XML