MS SQL Server/Azure - connect_cdc_mimix_share - 5.8

Connect CDC System Reference Guide

Product type
Software
Portfolio
Integrate
Product family
Connect
Product
Connect > Connect CDC (MIMIX Share)
Version
5.8
Language
English
Product name
Connect CDC
Title
Connect CDC System Reference Guide
Copyright
2024
First publish date
2003
ft:lastEdition
2024-02-12
ft:lastPublication
2024-02-12T15:11:59.486869

As shown below, Connect CDC supports distribution of most MS SQL Server datatypes. In the tables of supported datatypes in the following pages, the Numeric Length column shows the maximum supported number of numeric digits, while Display Length is the maximum number of characters, or the numeric length plus an included sign, if any.

An asterisk (*) means the length is taken from the column definition in the DBMS catalog. For decimal numeric types, the length is the number of positions to the left of the decimal point. For example, a NUMERIC(10,2) column has a length of 8.

Note: SQL Azure is supported as a target only. All the supported datatype information for MS SQL Server applies to SQL Azure as well.

Related Topics:

Datatype

Category

Numeric Length

Display Length

DATE

Date/Interval

 

10

TIME a

Date/Interval

 

8-16

DATETIME2

Date/Interval

 

19-27

DATETIMEOFFSET

Date/Interval

 

26-34

BIGINT

Integral Numeric

19

20

BINARY bc

Binary

 

*

BIT  

Integral Numeric

1

1

CHARACTER, CHAR

Character

 

d

DATETIME e

Date/Interval

 

20

DEC, DECIMAL

Decimal Numeric

*

 

DOUBLE PRECISION

Approximate Numeric

 

 

FLOAT

Approximate Numeric

 

 

IMAGE bcf

Long Binary

 

 

INT, INTEGER

Integral Numeric

10

11 g

MONEY

Decimal Numeric

 

NCHAR

Unicode

 

 

NTEXT cf

Long Unicode

 

 

NUMERIC

Decimal Numeric

*

 

NVARCHAR

Unicode

 

 

NVARCHAR(MAX)

Long Unicode

2 GB

 

REAL

Approximate Numeric

 

 

SMALLDATETIME e

Date/Interval

 

20

SMALLINT

Integral Numeric

5

6

SMALLMONEY

Decimal Numeric

i

 

TEXT cf

Long Character

 

 

TINYINT

Integral Numeric

3

3

UNIQUEIDENTIFIER j

Character

36

36

VARBINARY bc  

Binary

 

*

VARCHAR,CHARACTER VARYING

Character

 

d

VARCHAR(MAX)

Long Unicode

2 GB

 

XML

Long Unicode

2 GB

 

a Could vary in length because the scale can vary from 0 to 7.

b Cannot be a distribution key.

c Cannot be part of data enhancement, expressions or gate conditions.

d For MS SQL Server, <=8000. However, if defined length is greater than 255, column cannot be a distribution key or Expression column.

e See the table for the supported range of dates.

f For more information, see Distributing Long Column Data.

g 10 for IDENTITY.

h These values have a DBMS-defined implicit precision of 19 and scale of 4.

i These values have a DBMS-defined implicit precision of 10 and scale of 4.

j MS SQL Server targets only. See Distributing UNIQUEIDENTIFIER Columns.

User-defined Datatypes

There are cases where Connect CDC discovers new datatypes at runtime, for example, user-defined datatypes in MS SQL Server and Sybase, see User-defined Datatypes .

If such datatypes are used, they must be defined at all instances of the DBMS. For example, you cannot have two MS SQL Server instances that have different lists of user-defined datatypes.

Datatypes discovered at runtime are assumed to be compatible with any other datatype. The Connect CDC Director does not prevent you from mapping a user-defined datatype to any datatype supported by Connect CDC. If datatypes are in fact incompatible, you will receive runtime errors. Table registration and data distribution may fail.

To distribute a column that has a user-defined datatype, define the same datatype in the Connect CDC metabase database (omnirep), exactly as it is in the original database. Do this before you register the table in the Connect CDC Director.

If a user-defined datatype is not defined in the metabase, when a request is started and the XML metadata loaded, Connect CDC Kernel fails to populate the SQL metabase.

See the following example where user-defined datatypes (UDTs) are created.

create table qatest.dbo.UDT (
  key1 int primary key,
  col1 varchar(20),
  col2 udtvc20,
  col3 udtvc30,
  col4 udtvb20,
  col5 udtnum42,
  col6 udtnvc20,
  col7 udtint);

Define the datatypes with a tool such as the MS SQL Server Query Analyzer using sp_addtype. For example,

EXEC sp_addType UDTVB20, "varbinary(20)";
EXEC sp_addType UDTNUM42, "numeric(4,2)";
EXEC sp_addType UDTNVC20, "nvarchar(20)";
EXEC sp_addType UDTVC20, "varchar(20)";
EXEC sp_addType UDTVC30, "varchar(30)";
EXEC sp_addType UDTINT, "int";

These datatypes display in the Datatypes column on the Table Properties dialog box, Mapping Tab, as the following example shows:

Similarly, they are displayed on the Table Properties dialog box, Columns Tab.