create existing table

Component Integration Services includes changes to the connect to...diconnect command.

When a create existing table command is received, it is interpreted as a request to import metadata from the remote or external location of the object for updating system catalogs. Importing this metadata is performed by means of three RPCs sent to the remote server with which the object has been associated:

See also create existing table in the Reference Manual: Procedures

Server Class ASEnterprise

This table describes the allowable datatypes that can be used when mapping remote SAP ASE columns to local proxy table columns:

SAP ASE datatype conversions for create existing table

Remote SAP ASE Datatype

Allowable SAP ASE Datatypes

binary(n)

image, binary(n), and varbinary(n); if not image, the length must match

bit

bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint

char(n)

text, nchar( n), nvarchar( n), char( n), varchar(n), unichar, univarchar; if not text, the length must match

datetime

datetime, smalldatetime, char, and varchar

decimal(p, s)

bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint

float

bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint

image

image

int

bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint

money

bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint

nchar(n)

text, nchar( n), nvarchar( n), char( n), varchar( n); if not text, the length must match

numeric(p, s)

bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint

nvarchar(n)

text, nchar( n), nvarchar( n), char( n), varchar(n), unichar, univarchar; if not text, the length must match

real

bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint

smalldatetime

datetime, smalldatetime, char, and varchar

smallint

bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint

smallmoney

bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint

text

text, unitext

timestamp

timestamp

tinyint

bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint

unichar

char, varchar, unichar, univarchar, text, datetime, and smalldatetime

univarchar

char, varchar, unichar, univarchar, text, datetime, and smalldatetime

unitext

unitext

varbinary(n)

image, binary(n), and varbinary(n); if not image, the length must match

varchar(n)

text, nchar( n), nvarchar( n), char( n), varchar(n) unichar, univarchar; if not text, the length must match

date

time

bigint

Implicit: binary, varbinary, bit, tinyint, smallint, int, decimal, numeric, float, real, money, smallmoney

Explicit: char, varchar, unichar, univarchar

unsigned tinyint

Implicit: binary, varbinary, bit, tinyint, smallint, unsigned smallint, int, unsigned int, bigint, unsigned bigint, decimal, numeric, float, real money, smallmoney

Explicit: char, varchar, unichar, univarchar

Unsupported: text, image, date, time, datetime, smalldatetime

unsigned smallint

Implicit: binary, varbinary, bit, tinyint, smallint, unsigned smallint, int, unsigned int, bigint, unsigned bigint, decimal, numeric, float, real money, smallmoney

Explicit: char, varchar, unichar, univarchar

Unsupported: text, image, date, time, datetime, smalldatetime

unsigned int

Implicit: binary, varbinary, bit, tinyint, smallint, unsigned smallint, int, unsigned int, bigint, unsigned bigint, decimal, numeric, float, real money, smallmoney

Explicit: char, varchar, unichar, univarchar

Unsupported: text, image, date, time, datetime, smalldatetime

unsigned bigint

Implicit: binary, varbinary, bit, tinyint, smallint, unsigned smallint, int, unsigned int, bigint, unsigned bigint, decimal, numeric, float, real money, smallmoney

Explicit: char, varchar, unichar, univarchar

Unsupported: text, image, date, time, datetime, smalldatetime
Note: Component Integration Services only supports unitext with SAP ASE version 15.0 and higher.

Server Class ASAnywhere

This table describes the allowable datatypes that can be used when mapping remote SAP ASE columns to local proxy table columns:

SQL Anywhere datatype conversions for create existing table

Remote SQL Anywhere Datatype

Allowable SQL Anywhere Datatypes

binary(n)

image, binary(n), and varbinary( n); if not image, the length must match

bit

bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint

char(n)

text, nchar(n), nvarchar(n), char(n), varchar(n), unichar, univarchar; if not text, the length must match

datetime

datetime and smalldatetime

decimal(p, s)

bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint

float

bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint

image

image

int

bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint

money

bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint

numeric(p, s)

bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint

real

bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint

smalldatetime

datetime and smalldatetime

smallint

bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint

smallmoney

bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint

text

text

timestamp

timestamp

tinyint

bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint

varbinary(n)

image, binary(n), and varbinary(n), unichar, unitext, univarchar; if not image, the length must match

varchar(n)

text, nchar(n), nvarchar(n), char(n), varchar(n), unichar, univarchar; if not text, the length must match

date

time

bigint

Implicit: binary, varbinary, bit, tinyint, smallint, unsigned smallint, int, unsigned int, bigint, unsigned bigint, decimal, numeric, float, real money, smallmoney

Explicit: char, varchar, unichar, univarchar

Unsupported: text, image, date, time, datetime, smalldatetime

unsigned tinyint

Implicit: binary, varbinary, bit, tinyint, smallint, unsigned smallint, int, unsigned int, bigint, unsigned bigint, decimal, numeric, float, real money, smallmoney

Explicit: char, varchar, unichar, univarchar

Unsupported: text, image, date, time, datetime, smalldatetime

unsigned smallint

Implicit: binary, varbinary, bit, tinyint, smallint, unsigned smallint, int, unsigned int, bigint, unsigned bigint, decimal, numeric, float, real money, smallmoney

Explicit: char, varchar, unichar, univarchar

Unsupported: text, image, date, time, datetime, smalldatetime

unsigned int

Implicit: binary, varbinary, bit, tinyint, smallint, unsigned smallint, int, unsigned int, bigint, unsigned bigint, decimal, numeric, float, real money, smallmoney

Explicit: char, varchar, unichar, univarchar

Unsupported: text, image, date, time, datetime, smalldatetime

unsigned bigint

Implicit: binary, varbinary, bit, tinyint, smallint, unsigned smallint, int, unsigned int, bigint, unsigned bigint, decimal, numeric, float, real money, smallmoney

Explicit: char, varchar, unichar, univarchar

Unsupported: text, image, date, time, datetime, smalldatetime

nchar(n)

text, nchar(n), nvarchar(n), char(n), varchar(n), unichar, univarchar; if not text, the length must match

nvarchar(n)

text, nchar(n), nvarchar(n), char(n), varchar(n), unichar, univarchar; if not text, the length must match

Server Class ASIQ

  • text and image datatypes are supported by ASIQ version 12.6 and requires a license.

  • Behavior is the same as for server class ASAnywhere.

Server Class direct_connect

  • The RPC sp_columns queries the datatypes of the columns in the existing table.

  • Local column datatypes do not need to be identical to remote column datatypes, but they must be convertible as shown in the table below. If not, a column type error is raised and the command is aborted.

    DirectConnect datatype conversions for create existing table

    DirectConnect Datatype

    Allowable SAP ASE Datatypes

    binary(n)

    image, binary(n), varbinary(n); if the length does not match, the command is aborted

    binary(16)

    timestamp

    bit

    bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint

    char(n)

    text, nchar(n), nvarchar(n), char(n) and varchar(n), unichar, univarchar; if the length does not match, the command is aborted

    datetime

    datetime, smalldatetime

    decimal(p, s)

    bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint

    float

    bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint

    image

    image

    int

    bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint

    money

    bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint

    nchar(n)

    text, nchar(n), nvarchar(n), char(n) and varchar(n), unichar, univarchar; if the length does not match, the command is aborted

    numeric(p, s)

    bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint

    nvarchar(n)

    text, nchar(n), nvarchar(n), char(n) and varchar(n), unichar, univarchar; if the length does not match, the command is aborted

    real

    bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint

    smalldatetime

    datetime, smalldatetime

    smallint

    bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint

    smallmoney

    bit, decimal, float, int, money, numeric, real, smallint, smallmoney, and tinyint

    text

    text

    timestamp

    timestamp, binary(8), varbinary(8)

    unichar

    text, nchar(n), nvarchar(n), char(n), varchar(n), unichar, univarchar; if not text, the length must match

    univarchar

    text, nchar(n), nvarchar(n), char(n), varchar(n), unichar, univarchar; if not text, the length must match

    date

    time

    bigint

    UDB and DC/Microsoft support bigint.
  • Datatype information is passed in the CS_DATAFMT structure associated with the parameter. The following fields of the structure contain datatype information:

    • datatype – the CS_Library datatype representing the SAP ASE datatype. For example, CS_INT_TYPE.

    • usertype – the native DBMS datatype. sp_columns passes this datatype back to Component Integration Services during a create existing table command as part of its result set (see sp_columns in the Reference Manual). SAP ASE returns this datatype in the usertype field of parameters to assist the DirectConnect in datatype conversions.

Parent topic: Transact-SQLCommands