Data Type Mapping in ITableDefinition

When creating tables by using the ITableDefinition::CreateTable function, the SQLOLEDB consumer can specify Microsoft® SQL Server™ data types in the pwszTypeName member of the DBCOLUMNDESC array passed. If the consumer specifies a column’s data type by name, then OLE DB data type mapping, represented by the wType member of the DBCOLUMNDESC structure, is ignored.

When specifying new column data types with OLE DB data types using the DBCOLUMNDESC structure wType member, SQLOLEDB maps OLE DB data types as follows.

OLE DB data type	SQL Server data type	Additional information
DBTYPE_BOOL	bit
DBTYPE_BYTES	binary, varbinary, or image	SQLOLEDB inspects the ulColumnSize member of the DBCOLUMNDESC structure. Based on the value, and version of the SQL Server installation, SQLOLEDB maps the type to image. If the value of ulColumnSize is smaller than the maximum length of a binary data type column, then SQLOLEDB inspects the DBCOLUMNDESC rgPropertySets member. If DBPROP_COL_FIXED LENGTH is VARIANT_TRUE, SQLOLEDB maps the type to binary. If the value of the property is VARIANT_FALSE, SQLOLEDB maps the type to varbinary. In either case, the DBCOLUMNDESC ulColumnSize member determines the width of the SQL Server column created.
DBTYPE_CY	money
DBTYPE_ DBTIMESTAMP	datetime
DBTYPE_GUID	uniqueidentifier
DBTYPE_I2	smallint
DBTYPE_I4	int
DBTYPE_NUMERIC	numeric	SQLOLEDB inspects the DBCOLUMDESC bPrecision and bScale members to determine precision and scale for the numeric column.
DBTYPE_R4	real
DBTYPE_R8	float
DBTYPE_STR	char, varchar, or text	SQLOLEDB inspects the ulColumnSize member of the DBCOLUMNDESC structure. Based on the value and version of the SQL Server installation, SQLOLEDB maps the type to text. If the value of ulColumnSize is smaller than the maximum length of a multibyte character data type column, then SQLOLEDB inspects the DBCOLUMNDESC rgPropertySets member. If DBPROP_COL_FIXEDLENGTH is VARIANT_TRUE, SQLOLEDB maps the type to char. If the value of the property is VARIANT_ FALSE, SQLOLEDB maps the type to varchar. In either case, the DBCOLUMNDESC ulColumnSize member determines the width of the SQL Server column created.
DBTYPE_UI1	tinyint
DBTYPE_WSTR	nchar, nvarchar or ntext	Using DBTYPE_WSTR to define a column is supported for SQL Server version 7.0 servers only. SQLOLEDB inspects the ulColumnSize member of the DBCOLUMNDESC structure. Based on the value, SQLOLEDB maps the type to ntext. If the value of ulColumnSize is smaller than the maximum length of a Unicode character data type column, then SQLOLEDB inspects the DBCOLUMNDESC rgPropertySets member. If DBPROP_COL_FIXEDLENGTH is VARIANT_TRUE, SQLOLEDB maps the type to nchar. If the value of the property is VARIANT_ FALSE, SQLOLEDB maps the type to nvarchar. In either case, the DBCOLUMNDESC ulColumnSize member determines the width of the SQL Server column created.

Note When creating a new table, SQLOLEDB maps only the OLE DB data type enumeration values specified in the table above. Attempting to create a table with a column of any other OLE DB data type generates an error.