Data Type Mapping
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 native Microsoft® SQL Server™ data types by mapping data:
- From OLE DB data types to SQL Server native 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 in DELETE statements.
- From SQL Server native 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 involving remote data. The data type of remote_column corresponds to the mapped local data type as shown in the Data Type Mapping table.
The type rules for remote table columns in expressions can be described by this rule: a given remote column value is legal in a Transact-SQL expression if the corresponding mapped SQL Server data type in the following table is legal in the same context. For example, consider the expression:
local_column operator remote_column
local_column is a local table column and remote_column is a remote table column. This is a valid expression if operator is a valid operator for the local column's data type 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 native 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 mapping to a SQL Server type is determined by the DBTYPE and the DBCOLUMNFLAGS values describing the column. This information comes from the provider through either the COLUMNS schema rowset or through the IColumnsInfo interface. In the case of the COLUMNS schema rowset, the DATA_TYPE and COLUMN_FLAGS columns represent these values. In the case of the IColumnsInfo::GetColumnInfo interface, the wType and dwFlags members of the DBCOLUMNINFO structure represent this information.
The Data Type Mapping table shows data type mappings from the OLE DB provider to SQL Server. For a given column, given its DBTYPE and its DBCOLUMNFLAG value, the corresponding SQL Server data type can be found.
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_UI2 | numeric(5,0) | |
| DBTYPE_UI4 | numeric(10,0) | |
| DBTYPE_UI8 | 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_ISROWVER = true, DBCOLUMNFLAGS_ISFIXEDLENGTH = true, 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 > 4,000 characters | text |
| 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_UDT | Error | |
| DBTYPE_DATE | datetime | |
| DBTYPE_DBDATE | Error | |
| DBTYPE_DBTIME | Error | |
| DBTYPE_DBTIMESTAMP | datetime | |
| DBTYPE_ARRAY | Error | |
| DBTYPE_BYREF | Ignored | |
| DBTYPE_VECTOR | Error | |
| DBTYPE_RESERVED | Error |
1 numeric(p,s) indicates the SQL Server data type numeric with precision p and scale s.
Note If the data must be converted to a native data type different from the shown default, an explicit conversion (using either the CAST or CONVERT function) is required. For more information, see CAST and CONVERT.
Data Type Mapping from SQL Server to the OLE DB Provider
Native SQL Server data types map to OLE DB types using the same table name. A mapping from a SQL Server type S1 to a given OLE DB type T is allowed if either of these conditions exist:
- The corresponding mapping can be found in Table 1.
- 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 Table 1.