SSVARIANT Structure

Applies to: SQL Server (all supported versions) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Download OLE DB driver

The SSVARIANT structure, which is defined in msoledbsql.h, corresponds to a DBTYPE_SQLVARIANT value in the OLE DB Driver for SQL Server.

SSVARIANT is a discriminating union. Depending on the value of the vt member, the consumer can determine which member to read. vt values correspond to SQL Server data types. Therefore, the SSVARIANT structure can hold any SQL Server type. For more information about the data structure for standard OLE DB types, see Type Indicators.

Remarks

When DataTypeCompat==80, several SSVARIANT subtypes become strings. For example, the following vt values will appear in SSVARIANT as VT_SS_WVARSTRING:

  • VT_SS_DATETIMEOFFSET

  • VT_SS_DATETIME2

  • VT_SS_TIME2

  • VT_SS_DATE

When DateTypeCompat == 0, these types will appear in their native form.

For more information about SSPROP_INIT_DATATYPECOMPATIBILITY, see Using Connection String Keywords with OLE DB Driver for SQL Server.

The msoledbsql.h file contains variant access macros that simplify dereferencing the member types in the SSVARIANT structure. An example is V_SS_DATETIMEOFFSET, which you can use as follows:

memcpy(&V_SS_DATETIMEOFFSET(pssVar).tsoDateTimeOffsetVal, pDTO, cbNative);  
V_SS_DATETIMEOFFSET(pssVar).bScale = bScale;  

For the complete set of access macros for each member of the SSVARIANT structure, refer to the msoledbsql.h file.

The following table describes the members of the SSVARIANT structure:

Member OLE DB type indicator OLE DB C data type vt value Comments
vt SSVARTYPE Specifies the type of value contained in the SSVARIANT struct.
bTinyIntVal DBTYPE_UI1 BYTE VT_SS_UI1 Supports the tinyintSQL Server data type.
sShortIntVal DBTYPE_I2 SHORT VT_SS_I2 Supports the smallintSQL Server data type.
lIntVal DBTYPE_I4 LONG VT_SS_I4 Supports the intSQL Server data type.
llBigIntVal DBTYPE_I8 LARGE_INTEGER VT_SS_I8 Supports the bigintSQL Server data type.
fltRealVal DBTYPE_R4 float VT_SS_R4 Supports the realSQL Server data type.
dblFloatVal DBTYPE_R8 double VT_SS_R8 Supports the floatSQL Server data type.
cyMoneyVal DBTYPE_CY LARGE_INTEGER VT_SS_MONEY VT_SS_SMALLMONEY Supports the money and smallmoneySQL Server data types.
fBitVal DBTYPE_BOOL VARIANT_BOOL VT_SS_BIT Supports the bitSQL Server data type.
rgbGuidVal DBTYPE_GUID GUID VT_SS_GUID Supports the uniqueidentifierSQL Server data type.
numNumericVal DBTYPE_NUMERIC DB_NUMERIC VT_SS_NUMERIC Supports the numericSQL Server data type.
dDateVal DBTYPE_DATE DBDATE VT_SS_DATE Supports the dateSQL Server data type.
tsDateTimeVal DBTYPE_DBTIMESTAMP DBTIMESTAMP VT_SS_SMALLDATETIME VT_SS_DATETIME VT_SS_DATETIME2 Supports the smalldatetime, datetime, and datetime2SQL Server data types.
Time2Val DBTYPE_DBTIME2 DBTIME2 VT_SS_TIME2 Supports the timeSQL Server data type.

Includes the following members:

tTime2Val (DBTIME2)

bScale (BYTE) Specifies the scale for tTime2Val value.
DateTimeVal DBTYPE_DBTIMESTAMP DBTIMESTAMP VT_SS_DATETIME2 Supports the datetime2SQL Server data type.

Includes the following members:

tsDataTimeVal (DBTIMESTAMP)

bScale (BYTE) Specifies the scale for tsDataTimeVal value.
DateTimeOffsetVal DBTYPE_DBTIMESTAMPOFSET DBTIMESTAMPOFFSET VT_SS_DATETIMEOFFSET Supports the datetimeoffsetSQL Server data type.

Includes the following members:

tsoDateTimeOffsetVal (DBTIMESTAMPOFFSET)

bScale (BYTE) Specifies the scale for tsoDateTimeOffsetVal value.
NCharVal No corresponding OLE DB type indicator. struct _NCharVal VT_SS_WVARSTRING,

VT_SS_WSTRING
Supports the nchar and nvarcharSQL Server data types.

Includes the following members:

sActualLength (SHORT) Specifies the actual length for the string to which pwchNCharVal points. Does not include terminating zero.

sMaxLength (SHORT) Specifies the maximum length for the string to which pwchNCharVal points.

pwchNCharVal (WCHAR *) Pointer to the string.

rgbReserved (BYTE[5]) Specifies the collation information.

Unused members: dwReserved, and pwchReserved.
CharVal No corresponding OLE DB type indicator. struct _CharVal VT_SS_STRING,

VT_SS_VARSTRING
Supports the char and varcharSQL Server data types.

Includes the following members:

sActualLength (SHORT) Specifies the actual length for the string to which pchCharVal points. Does not include terminating zero.

sMaxLength (SHORT) Specifies the maximum length for the string to which pchCharVal points.

pchCharVal (CHAR *) Pointer to the string.

rgbReserved (BYTE[5]) Specifies the collation information.

Unused members:

dwReserved, and pwchReserved.
BinaryVal No corresponding OLE DB type indicator. struct _BinaryVal VT_SS_VARBINARY,

VT_SS_BINARY
Supports the binary and varbinarySQL Server data types.

Includes the following members:

sActualLength (SHORT) Specifies the actual length for the data to which prgbBinaryVal points.

sMaxLength (SHORT) Specifies the maximum length for the data to which prgbBinaryVal points.

prgbBinaryVal (BYTE *) Pointer to the binary data.

Unused member: dwReserved.
UnknownType UNUSED UNUSED UNUSED UNUSED
BLOBType UNUSED UNUSED UNUSED UNUSED

Known issues

Possible narrow string data corruption

Before version 18.4 of the OLE DB driver, insertion into a sql_variant column could result in data corruption on the server if all of the following conditions were true:

  • The client machine code page didn't match the database collation code page.
  • The client buffer to insert contained non-ASCII narrow string characters encoded in the client code page.
  • Either of the following conditions were true:
    • The pwszDataSourceType field in the DBPARAMBINDINFO structure describing the parameter corresponding to the sql_variant column was set to L"DBTYPE_SQLVARIANT", L"DBTYPE_VARIANT", or L"sql_variant". For details, see: ICommandWithParameters::SetParameterInfo.

      or

    • The parameterized SQL query used for insertion was prepared.

More specifically, the OLE DB driver didn't translate the data to the database collation code page before inserting it. However, the driver wrongly indicated to the server that the data was encoded in the database collation code page. This behavior resulted in a mismatch between the data and its corresponding code page stored in the sql_variant column.

Similarly, upon retrieval of the same value, the OLE DB driver didn't translate strings to the client code page. However, since the inserted data was already in the client code page (see the paragraph above), the client application could interpret the data correctly. Even so, applications using other drivers would retrieve these values in a corrupted format. The corruption occurs because other drivers interpreted the string in the database collation code page and attempted to translate it to the client code page.

Starting from version 18.4, the OLE DB Driver translates the narrow strings to the database collation code page before the insertion. Similarly, the driver translates the data back to the client code page upon retrieval. As a result, client applications that rely on the bug mentioned above might experience issues while retrieving data that is inserted using an earlier version of the OLE DB Driver. The recovery procedure below aims to provide guidance to resolve these issues.

Recovery procedure

Important

Before performing the recovery steps below, make sure to back up your existing data.

If your application experiences issues retrieving data from a sql_variant column after switching to version 18.4 of the OLE DB driver, the corrupted data needs to be modified to have the same collation as the database in which the data is stored. The following script can be used to recover a single value from a sql_variant column. The script is a template and you need to adjust it to fit your scenario.

Important

Since the original code page of the data isn't stored, you need to tell the server how the data was initially encoded. To do so, execute the script within the context of a database that has the same code page as the code page of the client which initially inserted the data. For example, if the corrupted data was inserted from a client configured with code page 932, the following script needs to be executed within the context of a database with a Japanese collation (e.g. Japanese_XJIS_100_CS_AI).

/*
    Description:
        Template that can be used to recover the corrupted value inserted into the sql_variant column.

    Scenario:
        The database is named [YourDatabase] and it contains a table named [YourTable], which contains the corrupted value.
        Schema is named [dbo].
        The corrupted value is stored in a column of type sql_variant named [YourColumn].
        The corrupted value is sql_variant of BaseType char. For details on sql_variant properties, see:
            https://learn.microsoft.com/sql/t-sql/functions/sql-variant-property-transact-sql
*/

-- Base type in sql_variant can hold a maximum of 8000 bytes
-- For details see: 
--  https://learn.microsoft.com/sql/t-sql/data-types/sql-variant-transact-sql#remarks
DECLARE @bin VARBINARY(8000)

-- In the following lines we convert the sql_variant base type to binary.
-- <FilterExpression>
--      Is a placeholder and must be replaced with an expression that filters a single corrupted value to be recovered.
--      Therefore, the expression must result in a single value being returned only.
SET @bin = (SELECT CAST([YourColumn] AS VARBINARY(8000)) FROM [YourDatabase].[dbo].[YourTable] WHERE <FilterExpression>)

-- In the following lines we store the binary value in char(59) (a fixed-size character data type).
-- IMPORTANT NOTE: 
--      This example assumes the corrupted sql_variant's base type is char(59).
--      You MUST adjust the type (that is, char/varchar) and size to match your scenario exactly.
DECLARE @char CHAR(59)
SET @char = CAST((@bin) AS CHAR(59))
DECLARE @sqlvariant sql_variant

-- The following lines recover the corrupted value by translating the value to the collation of the database.
-- <DBCollation>
--      Must be replaced with the collation (for example, Latin1_General_100_CI_AS_SC_UTF8) of the database holding the data.
SET @sqlvariant = @char collate <DBCollation>

-- Finally, we update the corrupted value with the recovered value.
-- "<FilterExpression>"
--      Is a placeholder and must be replaced with an expression that filters a single corrupted value to be recovered.
--      Therefore, the expression must result in a single value being returned only.
UPDATE [YourDatabase].[dbo].[YourTable] SET [YourColumn] = @sqlvariant WHERE <FilterExpression>

See Also

Data Types (OLE DB)