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
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.) |
1numeric(p,s) indicates the SQL Server data type numeric with precision p and scale s.
Note
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.
Data Type Mapping from SQL Server to the OLE DB Provider
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.
See Also
Concepts
Guidelines for Using Distributed Queries
Distributed Queries