Sql_variant Column requires Conversion

SQL Server Administration
1

Hi experts, so somewhere in this block, it encounters a sql_variant data type and fails with:
"Msg 257, Level 16, State 3, Line 25
Implicit conversion from data type sql_variant to varchar is not allowed. Use the CONVERT function to run this query."

  1. except for DateInstalled, all table columns are defined as varchar
  2. It fails on the Insert statement. How can I know which line/column is failing?
  3. After playing with the CONVERT for sql_variant, I can't get the syntax right.

Thanks for any tips.

Here is the block of code that fails:

CREATE TABLE #CPUValues(
[index] SMALLINT,
[description] VARCHAR(128),
[server_cores] SMALLINT,
[value] VARCHAR(5)
)

CREATE TABLE #MemoryValues(
[index] SMALLINT,
[description] VARCHAR(128),
[server_memory] DECIMAL(10,2),
[value] VARCHAR(64)
)

INSERT INTO #CPUValues
EXEC xp_msver 'ProcessorCount'

INSERT INTO #MemoryValues
EXEC xp_msver 'PhysicalMemory'

INSERT INTO [dbo].[InstanceConfigurations]
([ServerName]
,[Version]
,[Edition]
,[SPLevel]
,[BuildNumber]
,[AuthenticationType]
,[Port]
,[Collation]
,[DateInstalled]
,[BackupCompressionDefault]
,[ServerMemory]
,[MinSQLMemory]
,[MaxSQLMemory]
,[ServerCores]
,[SQLCores])

SELECT
SERVERPROPERTY('SERVERNAME') as Server,
(SELECT
CASE
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) like '8%' THEN 'SQL Server 2000'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) like '9%' THEN 'SQL Server 2005'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) like '10.0%' THEN 'SQL Server 2008'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) like '10.5%' THEN 'SQL Server 2008 R2'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) like '11%' THEN 'SQL Server 2012'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) like '12%' THEN 'SQL Server 2014'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) like '13%' THEN 'SQL Server 2016'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) like '14%' THEN 'SQL Server 2017'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) like '15%' THEN 'SQL Server 2019'
ELSE 'UNKNOWN'
END) as Version,

(SELECT SUBSTRING(CONVERT(VARCHAR(255),SERVERPROPERTY('EDITION')),0,CHARINDEX('Edition',CONVERT(VARCHAR(255),SERVERPROPERTY('EDITION')))) + 'Edition') AS Edition,
SERVERPROPERTY('ProductLevel') AS 'Service_Pack_Level',
SERVERPROPERTY('ProductVersion') AS 'Build_Number',
(SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
WHEN 1 THEN 'Windows Authentication'
WHEN 0 THEN 'Windows and SQL Server Authentication'
END) as AuthenticationType,

(SELECT DISTINCT local_tcp_port FROM sys.dm_exec_connections WHERE session_id = @@SPID) As Port,
(SELECT SERVERPROPERTY('collation') AS SQLServerCollation) As Collation,

(SELECT create_date FROM sys.server_principals WHERE sid = 0x010100000000000512000000) As DateInstalled,

(select [value_in_use] from sys.configurations
where name = 'Backup compression default') As BackupCompressionDefault,

(SELECT ROUND(CONVERT(DECIMAL(10,2),server_memory/1024.0),1) FROM #MemoryValues) AS server_memory,

(SELECT [value] FROM sys.configurations WHERE name like '%min server memory%') AS min_server_memory,
(SELECT [value] FROM sys.configurations WHERE name like '%max server memory%') AS max_server_memory,
server_cores,

(SELECT COUNT(*) AS 'sql_cores' FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE') AS sql_cores

FROM #CPUValues
LEFT JOIN (
SELECT
CASE
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) like '8%' THEN 'SQL Server 2000'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) like '9%' THEN 'SQL Server 2005'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) like '10.0%' THEN 'SQL Server 2008'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) like '10.5%' THEN 'SQL Server 2008 R2'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) like '11%' THEN 'SQL Server 2012'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) like '12%' THEN 'SQL Server 2014'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) like '13%' THEN 'SQL Server 2016'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) like '14%' THEN 'SQL Server 2017'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) like '15%' THEN 'SQL Server 2019'
ELSE 'UNKNOWN'
END AS sql_version
) AS v ON 1 = 1

DROP TABLE #CPUValues
DROP TABLE #MemoryValues

2

Run the queries without the insert and see if the data types returned match corresponding data types on the target tables, a visual check

3

Thanks for you idea, yosiaz. When I run without the insert, the data returns without error and it looks good.
sys.configurations contains 4 columns that have the sql_variant data type:
value sql_variant null,
minimum sql_variant null,
maximum sql_variant null,
value_in_use sql_variant null

Do you know how I can use the CONVERT function to convert each to varchar?
Thanks.

4

add this to your query

into dbo.lockdown
FROM #CPUValues

then examine the structure of lockdown

SERVERPROPERTY('SERVERNAME') as Server

so use this instead ?

@@SERVERNAME as Server,

1 Like
5

After much experimentation, I found 6 values/columns that Microsoft stores as the sql_variant data type:
SPLevel - fails if varchar - must be sql_variant
BuildNumber - fails if varchar - must be sql_variant
Collation - fails if varchar - must be sql_variant
BackupCompressionDefault - fails if varchar - must be sql_variant
MinSQLMemory - fails if Decimal(10,2) - must be sql_variant
MaxSQLMemory - fails if Decimal(10,2) - must be sql_variant

So for example - how can I use the CONVERT function here to convert to varchar?

Select SERVERPROPERTY('ProductVersion') AS 'Build_Number'

6

Did you read what I posted?

7

Yes, I created both temp tables as physical tables and ran the inserts. They don't contain any sql_variant data types.

I changed the 6 data types in the table to sql_variant and the insert runs successfully now.
I would still like to know how to use the CONVERT function so I could convert to varchar and insert into varchar columns.
Thanks for your ideas..

8

This is what Microsoft says about the sql_variant data type:

Converting sql_variant Data

When handling the sql_variant data type, SQL Server supports implicit conversions of objects with other data types to the sql_variant type. However, SQL Server does not support implicit conversions from sql_variant data to an object with another data type.

9

Select CAST(SERVERPROPERTY('ProductVersion') AS varchar(500)) AS 'Build_Number'
or:
Select CONVERT(varchar(500), SERVERPROPERTY('ProductVersion')) AS 'Build_Number'

1 Like
10

I am not sure why you are so fixated on converting it ?