There are a number of data types that are currently unsupported on PDW. Among those are SQL_VARIANT. The interesting part of that is that SQL_VARIANT along with several other unsupported data types are found all over the place in system tables in PDW. There is a simple solution, convert the value to something like VARCHAR. Well this wouldn’t be a blog post if that was all I had to say on the matter.

Turns out that you can select the data just fine, convert it in a select just fine, but for some odd reason not do an insert with it just fine. Let’s take the partitions range table for this example.

SELECT

CONVERT(VARCHAR(200), value, 101) AS ConvertedText

FROM sys.partition_range_values

Try to insert that into a temp table and bad things happen.

--Create a temp table

CREATE TABLE #Partitions

(

Value VARCHAR(200)

)

WITH (LOCATION = USER_DB)

--Load the temp table

INSERT INTO #Partitions

SELECT

CONVERT(VARCHAR(200), value, 101) AS ConvertedText

FROM sys.partition_range_values

--Select the records

SELECT * FROM #Partitions

--Drop the table

DROP TABLE #Partitions

You will get this error when it runs the insert statement even though it is clearly being converted.

The workaround: CTAS works like a charm!

CREATE TABLE TestPartition

WITH (DISTRIBUTION = REPLICATE) AS

SELECT

CONVERT(VARCHAR(200), value, 101) AS ConvertedText

FROM sys.partition_range_values

SELECT * FROM TestPartition

DROP TABLE TestPartition

For those who require visual proof:

Happy CTASing and happy Friday!