Export (0) Print
Expand All
1 out of 4 rated this helpful - Rate this topic

Data Type Mapping with Distributed Queries

An OLE DB provider exposes the data types of its data in terms of OLE DB type identifiers called DBTYPEs. Data types are converted between OLE DB data types and SQL Server system data types by mapping data:

  • From OLE DB data types to SQL Server system data types. This conversion occurs when SQL Server reads data from the OLE DB data source, either in SELECT statements or in the reading side of UPDATE, INSERT, or DELETE statements.
  • From SQL Server system data types to OLE DB data types. This conversion occurs when SQL Server writes data, mostly in INSERT or UPDATE statements, into the OLE DB data source in which the modified table is a remote table.

Data type mapping from the OLE DB provider to SQL Server defines the allowed comparisons and expressions, and the valid explicit conversions that involve remote data. The mapping is shown in the table that follows.

The type validity for remote table columns in expressions can be summarized by this rule: a remote column value is valid in a Transact-SQL expression if the corresponding mapped SQL Server data type in the Data Type Mapping table is valid in the same context.

For example, consider the expression: local_column OPERATOR remote_column. In this expression, local_column is a local table column and remote_column is a remote table column. The expression is valid if OPERATOR is a valid operator for the data type of the local column and for the data type to which the DBTYPE of remote_column maps.

Similarly, CAST(remote_column AS data_type_1) is allowed if the DBTYPE of remote_column maps to the SQL Server system data type data_type_2 and explicit conversion from data_type_2 to data_type_1 is allowed. For example, a column of data type DBTYPE_DATE on the provider side can be converted to a datetime column in SQL Server. However, the DBTYPE_DATE data cannot be converted directly to varchar.

The following table shows the data type mapping table. By using the DBTYPE indicator and its DBCOLUMNFLAGS value of a column, you can find the corresponding SQL Server data type.

DBTYPE DBCOLUMNFLAGS SQL Server data type

DBTYPE_I1

numeric(3, 0)1

DBTYPE_I2

smallint

DBTYPE_I4

int

DBTYPE_I8

bigint

DBTYPE_UI1

tinyint

DBTYPE_UI1

numeric(5,0)

DBTYPE_UI1

numeric(10,0)

DBTYPE_UI1

numeric(20,0)

DBTYPE_R4

float

DBTYPE_R8

real

DBTYPE_NUMERIC

numeric

DBTYPE_DECIMAL

decimal

DBTYPE_CY

money

DBTYPE_BSTR

DBCOLUMNFLAGS_ISLONG = true

ntext

DBTYPE_BSTR

DBCOLUMNFLAGS_ISFIXEDLENGTH = true

nchar

DBTYPE_BSTR

DBCOLUMNFLAGS_ISFIXEDLENGTH = false

nvarchar

DBTYPE_IDISPATCH

Error

DBTYPE_ERROR

Error

DBTYPE_BOOL

bit

DBTYPE_VARIANT

nvarchar(4000)

DBTYPE_IUNKNOWN

Error

DBTYPE_GUID

uniqueidentifier

DBTYPE_BYTES

DBCOLUMNFLAGS_ISLONG = true or maximum column size > 8,000 bytes.

image

DBTYPE_BYTES

DBCOLUMNFLAGS_ISLONG = true and column size is unlimited length.

varbinary(max)

DBTYPE_BYTES

DBCOLUMNFLAGS_ISROWVER = true, DBCOLUMNFLAGS_ISFIXEDLENGTH = true, and column size = 8

timestamp

DBTYPE_BYTES

DBCOLUMNFLAGS_ISFIXEDLENGTH = true

binary

DBTYPE_BYTES

DBCOLUMNFLAGS_ISFIXEDLENGTH = false

varbinary

DBTYPE_STR

DBCOLUMNFLAGS_ISFIXEDLENGTH = true

char

DBTYPE_ STR

DBCOLUMNFLAGS_ISFIXEDLENGTH = false

varchar

DBTYPE_STR

DBCOLUMNFLAGS_ISLONG = true or maximum column size > 8,000 characters.

text

DBTYPE_STR

DBCOLUMNFLAGS_ISLONG = true and column size is unlimited length.

varchar(max)

DBTYPE_WSTR

DBCOLUMNFLAGS_ISFIXED

nchar

DBTYPE_WSTR

DBCOLUMNFLAGS_ISFIXEDLENGTH = false

nvarchar

DBTYPE_WSTR

DBCOLUMNFLAGS_ISLONG = true or maximum column size > 4,000 characters.

ntext

DBTYPE_WSTR

DBCOLUMNFLAGS_ISLONG = true and column size is unlimited length.

nvarchar(max)

DBTYPE_UDT

Equivalent SQL Server user-defined type, if one is registered.

DBTYPE_DATE

datetime

DBTYPE_DBDATE

datetime (Compatibility level is less than 9.0.)

DBTYPE_DBTIME

datetime

DBTYPE_DBTIMESTAMP

datetime

DBTYPE_ARRAY

Error

DBTYPE_BYREF

Ignored

DBTYPE_VECTOR

Error

DBTYPE_RESERVED

Error

DBTYPE_XML

xml (Allowed only in pass-through queries.)

1 numeric(p,s) indicates the SQL Server data type numeric with precision p and scale s.

ms175970.note(en-US,SQL.90).gifNote:
If the data must be converted to a SQL Server data type that is different from the shown default, an explicit conversion, by using either the CAST or CONVERT function, is required. For more information, see CAST and CONVERT (Transact-SQL).

The DBTYPE indicator and DBCOLUMNFLAGS value information comes from the provider through either the COLUMNS schema rowset or through the IColumnsInfo interface. For the COLUMNS schema rowset, the DATA_TYPE and COLUMN_FLAGS columns represent the DBTYPE and DBCOLUMNFLAGS values. For the IColumnsInfo::GetColumnInfo interface, the wType and dwFlags members of the DBCOLUMNINFO structure represent these values.

SQL Server system data types map to OLE DB types by using the mapping shown in the previous table. A mapping from a SQL Server type S1 to a specific OLE DB type T is allowed if either of these conditions exist:

  • The corresponding mapping can be found in the data type mapping table.
  • There is an allowed implicit conversion of the data type S1 to another SQL Server data type S2 and a mapping from S2 to T is defined in the data type mapping table.
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.