In fetching sql_variant data as DBTYPE_SQLVARIANT, the session property SSPROP_ALLOWNATIVEVARIANT must be set to TRUE. If SSPROP_ALLOWNATIVEVARIANT property is set to TRUE, the column type is returned as DBTYPE_SQLVARIANT, in which case the buffer will hold the SSVARIANT structure. If SSPROP_ALLOWNATIVEVARIANT property is FALSE DBTYPE_SQLVARIANT is not supported. When IColumnsInfo is used to obtain column information for binding purposes, if the SSPROP_ALLOWNATIVEVARIANT session property is FALSE (default value), DBTYPE_VARIANT is returned for sql_variant columns. IColumnsInfo can also be used to get the column information and use that to do the binding. In fetching data, you can specify explicitly what kind of data type should be returned for a column or for a parameter. Provider-Specific Property SSPROP_ALLOWNATIVEVARIANT
The session property SSPROP_ALLOWNATIVEVARIANT must also be set to TRUE. The SSVARIANT structure contains all of the subtypes that match the subtypes of the sql_variant data type. When sql_variant data is fetched in as DBTYPE_SQLVARIANT, it is stored in a provider-specific SSVARIANT structure.
MICROSOFT ACCESS DATA TYPES OLEDB DRIVER
To support the sql_variant data type, the OLE DB Driver for SQL Server exposes a provider-specific data type called DBTYPE_SQLVARIANT. The sql_variant data must then be fetched as DBTYPE_SQLVARIANT in order for all the subtypes to match. But the subtypes in the VARIANT structure may not map to subtypes defined in the sql_variant data type. When sql_variant data is fetched as DBTYPE_VARIANT, it is put in a VARIANT structure in the buffer. The sql_variant data type is similar to the Variant data type in Microsoft Visual BasicĀ® and the DBTYPE_VARIANT, DBTYPE_SQLVARIANT in OLEDB. For example, the column can contain smallint values for some rows, float values for other rows, and char/ nchar values in the remainder. An instance of sql_variant data also cannot have sql_variant as its underlying base data type. NET Framework common language runtime (CLR) user-defined types. The sql_variant objects can hold data of any SQL Server data type except text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, timestamp, and Microsoft. The OLE DB Driver for SQL Server supports consumer-requested data conversions as shown in the illustration. In rowsets and as parameter values, the OLE DB Driver for SQL Server represents SQL Server data by using the following OLE DB defined data types, reported in the functions IColumnsInfo::GetColumnInfo and ICommandWithParameters::GetParameterInfo.
Applies to: SQL Server (all supported versions) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)