SQL Server Error: Com.microsoft.sqlserver.jdbc.SQLServerException:The "Variant" data type is not supported.

Query a table structure in SQL SERVER with the following SQL statement:

SELECT    tb.name AS tableName,    col.name AS columnName,    col.max_length AS length,    col.is_nullable AS isNullable,    t.name AS type,    (    SELECT        TOP 1 ind.is_primary_key    FROM        sys.index_columns ic        LEFT JOIN sys.indexes ind ON ic.object_id = ind.object_id AND ic.index_id= ind.index_id AND ind.name LIKE ‘PK_%‘    WHERE        ic.object_id = tb.object_id AND ic.column_id= col.column_id    ) AS isPrimaryKey,    com.value AS commentFROM    sys.TABLES tb    INNER JOIN sys.columns col ON col.object_id = tb.object_id    LEFT JOIN sys.types t ON t.user_type_id = col.user_type_id    LEFT JOIN sys.extended_properties com ON com.major_id = col.object_id    AND com.minor_id = col.column_idWHERE    tb.name = ‘表名‘

The SQL can execute normally, but when the SQL is put into the jdbctemplate to perform the Times error:

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The "variant" data type is not supported.

The reason is that there is a type property behind the SQL statement select sql_variant and it is not supported in JDBC. Using the sp_columns command to finally isolate the sys.extended_properties value properties of the table TYPE_NAME is sql_variant type, SQL is as follows:

sp_columns extended_properties

The workaround is CONVERT to use the function to turn the property into a varchar type.

The usage of the CONVERT function is referenced in the use of the CONVERT () function in SQL Server.

The modified SQL statement is:

SELECT    tb.name AS tableName,    col.name AS columnName,    col.max_length AS length,    col.is_nullable AS isNullable,    t.name AS type,    (    SELECT        TOP 1 ind.is_primary_key    FROM        sys.index_columns ic        LEFT JOIN sys.indexes ind ON ic.object_id = ind.object_id AND ic.index_id= ind.index_id AND ind.name LIKE ‘PK_%‘    WHERE        ic.object_id = tb.object_id AND ic.column_id= col.column_id    ) AS isPrimaryKey,    CONVERT(varchar(200), com.value) AS commentFROM    sys.TABLES tb    INNER JOIN sys.columns col ON col.object_id = tb.object_id    LEFT JOIN sys.types t ON t.user_type_id = col.user_type_id    LEFT JOIN sys.extended_properties com ON com.major_id = col.object_id    AND com.minor_id = col.column_idWHERE    tb.name = ‘表名‘

Reference:

Com.microsoft.sqlserver.jdbc.SQLServerException:The "Variant" data type is not supported.

SQL Server Error: Com.microsoft.sqlserver.jdbc.SQLServerException:The "Variant" data type is not supported.