SSVARIANT Structure

Applies to: SQL Server 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:

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

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.

/*
    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)