Subscribe Bookmark RSS Feed

Heads Up: TSQL sql_variant data type WILL NOT import into JMP


Community Trekker


Jun 23, 2011

I just discovered that JMP (version 8 at least) will not import column data type "sql_variant" into its tables.

I spent all afternoon trying to figure out why two columns would not load in my JMP ODCB SQL query. I finally noticed that those two columns are "sql_variant" data type.

Very easy fix though: just convert your column data type in SQL using CAST. For example, if your "sql_variant" column is "Blah", SELECT it as "CAST( Blah AS VARCHAR(MAX))" or int, datetime, etc...

I came across this problem today, only I can't CAST or CONVERT on the SQL side because the data type is a little endian that contains an array of data. I can, however, handle this data type on the JMP side using a BLOB function.

So, if I can't bring the data type into JMP using OPEN DATABASE(), is there a way to run the BLOB function on the database side (with a direct reference to the database maybe)? Breaking up the array on the database side is a possibility, but I will have to sit though a lecture about how I am wrecking the elegance on their end.