Data Type Mapping with Distributed Queries
An OLE DB provider exposes the data types of its data according to 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 the following 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 |
date, datetime, datetime2, datetimeoffset |
|
DBTYPE_DBDATE |
date, datetime, datetime2, datetimeoffset (Compatibility level is less than 9.0.) |
|
DBTYPE_DBTIME |
time, datetime, datetime2, datetimeoffset |
|
DBTYPE_DBTIME_EX |
time, datetime2, datetimeoffset |
DBTYPE_DBTIMESTAMP |
time, date, datetime2, datetimeoffset, datetime |
|
|---|---|---|
DBTYPE_DBTIMESTAMP |
time, date, datetime2, datetimeoffset |
|
DBTYPE_DBTIMESTAMP |
time, date, datetime2, datetimeoffset |
|
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.
SQL Server 2008 Mapping of Date and Time Date Received from a Remote Server
The following table shows the date and time data type mapping for data sent from an OLE DB data source to an instance of SQL Server 2008. This conversion occurs when SQL Server 2008 reads data from the OLE DB data source, either in SELECT statements or in the reading side of UPDATE, INSERT, or DELETE statements. If a remote column is of a date, time, dateime2, or datetimeoffset data type, that type is returned if the database compatibility level is 100 or higher. If the compatibility level is lower, SQL Server 2008 makes an implicit conversion to datetime.
OLE DB Type |
If the remote server is SQL Server 2008 return: |
If the remote server is SQL Server 2005 or SQL Server 2000 return: |
If the remote server is not SQL Server and the local SQL Server 2008 database compatibility level is 90 return: |
If the remote server is not SQL Server and the local SQL Server 2008 database compatibility level is 100 return: |
|---|---|---|---|---|
DBTYPE_DBTIMESTAMP without dwFlags being set as DBPARAMFLAGS_SS_ISVARIABLESCALE |
datetime (remote column could be either datetime or smalldatetime) |
datetime (remote column could be either datetime or smalldatetime) |
datetime |
datetime2(7) |
DBTYPE_DBTIMESTAMP with dwFlags being set as DBPARAMFLAGS_SS_ISVARIABLESCALE |
datetime2 |
Not applicable |
Not applicable |
Not applicable |
DBTYPE_DBDATE |
date |
Not applicable |
datetime |
date |
DBTYPE_DBTIME |
time(0) |
Not applicable |
datetime |
time(0) |
DBTYPE_DBTIME2 |
time(n) |
Not applicable |
Not applicable |
Not applicable |
DBTYPE_DBTIMESTAMPOFFSET |
datetimeoffset |
Not applicable |
Not applicable |
Not applicable |
SQL Server 2008 Mapping of Date and Time Date Sent to a Remote Server
The following table shows the date and time data type mapping of data sent from an instance of SQL Server 2008 to an OLE DB data destination. This conversion occurs when SQL Server 2008 writes data, mostly in INSERT or UPDATE statements, into the OLE DB data source in which the modified table is a remote table.
SQL Server 2008 data type |
If the remote server is SQL Server 2008 bind with: |
If the remote server is SQL Server 2005 or SQL Server 2000 bind with: |
If the remote server is not SQL Server bind with: |
|---|---|---|---|
datetime, smalldatetime |
DBTYPE_DBTIMESTAMP without dwFlags being set as DBPARAMFLAGS_SS_ISVARIABLESCALE |
DBTYPE_DBTIMESTAMP without dwFlags being set as DBPARAMFLAGS_SS_ISVARIABLESCALE |
DBTYPE_DBTIMESTAMP without dwFlags being set as DBPARAMFLAGS_SS_ISVARIABLESCALE |
datetime2(n) |
DBTYPE_DBTIMESTAMP with dwFlags being set as DBPARAMFLAGS_SS_ISVARIABLESCALE |
DBTYPE_DBTIMESTAMP without dwFlags being set as DBPARAMFLAGS_SS_ISVARIABLESCALE |
DBTYPE_DBTIMESTAMP without dwFlags being set as DBPARAMFLAGS_SS_ISVARIABLESCALE |
date |
DBTYPE_DBDATE |
DBTYPE_DBDATE |
DBTYPE_DBDATE |
time(0) |
DBTYPE_DBTIME |
DBTYPE_DBTIME |
DBTYPE_DBTIME |
time(n) (0 < n <= 7) |
DBTYPE_DBTIMESTAMP (with pending date portion) with dwFlags being set as DBPARAMFLAGS_SS_ISVARIABLESCALE |
DBTYPE_DBTIMESTAMP without dwFlags being set as DBPARAMFLAGS_SS_ISVARIABLESCALE |
DBTYPE_DBTIMESTAMP (with pending date portion) |
datetimeoffset |
DBTYPE_DBTIMESTAMPOFFSET |
Not applicable |
Not applicable |
SQL Server 2008 Remote Query Execution with Date and Time Data Types
SQL Server 2008 executes queries that reference remote objects having the time, date, datetime2, or datetimoffset data types, either on the local or the remote server. The decision depends on the version or provider of the remote server and the type of reference. Reference types considered are a remote column or a constant, variable or local column.
Non-SQL Server Providers
Support is partial for time, date, and datetime2 data types when similar types exist on the remote server for non-SQL Server providers. These providers do not have a way of declaring their support for these types.
The following table shows whether a query is executed on the remote or local server. The first columns show the data type on the local server. The second column shows the corresponding OLE DB data type that the local instance of SQL Server 2008 uses for the remote server. The last three columns show whether the query is executed on the remote or local server. Execution location depends on the version or the type of the remote server.
Local server data type |
Remote server OLE DB data type that is used by the local SQL Server 2008 |
SQL Server 2008 or a later version of remote server |
SQL Server 2005 remote server |
Non-MSSQL remote server |
|---|---|---|---|---|
datetime |
DBTYPE_DBTIMESTAMP |
Remote |
Remote |
Remote |
smalldatetime |
DBTYPE_DBTIMESTAMP |
Remote |
Remote |
Remote |
datetime2 |
DBTYPE_DBTIMESTAMP |
Remote |
Local |
Remote |
datetimeoffset |
DBTYPE_DBTIMESTAMPOFFSET |
Remote |
Local |
Local |
date |
DBTYPE_DBDATE |
Remote |
Local |
Remote |
time(>0) |
DBTYPE_DBTIME2 |
Remote |
Local |
Local |
time(0) |
DBTYPE_DBTIME2 |
Remote |
Local |
Remote |
See Also