SQL Server sql_variant type / Microsoft Access gotcha

We have it. Might as well use it.

I found a really cool way to use the sql_variant type the other day, but ran into a problem using SQL Server and Microsoft Access. The client where I am located stores lookup data in Access, and then pushes the data from a .mdb into many client databases – because they are a global company with huge rollouts the implementation team keeps track of static/lookup data this way, and then moves it to each database as needed.  Not at all a bad way to store this data in VSS I suppose. Anyway, you need to remember that Microsoft Access stores sql_variant as text data type. And because the base data type of sql_variant gets set when and only when a value enters the row for a given sql_variant column, I found that my really cool way to use the type turned into somewhat of a pain. Run the following:

DROP TABLE PastDue

GO

CREATE TABLE PastDue

       (id int

       ,BucketLo sql_variant

       ,BucketHi sql_variant

       ,Descr varchar (100)

       )

GO    

INSERT INTO PastDue VALUES  (1, 1,15, '1-15 days past due')

INSERT INTO PastDue VALUES  (2, 16,30, '16-30 days past due')

INSERT INTO PastDue VALUES  (3, 31,60, '31-60 days past due')

INSERT INTO PastDue VALUES  (4, 61,90, '61-90 days past due')

GO

SELECT *

FROM PastDue

WHERE 5 BETWEEN BucketLo and BucketHi

GO

SELECT   SQL_VARIANT_PROPERTY(BucketLo,'BaseType') AS 'Base Type'

FROM  PastDue

GO

We’re plenty good here, but let’s try this one now. The first row is a replica of how Microsoft Access inserted my rows:

DELETE PastDue

INSERT INTO PastDue VALUES  (1, '1','15', '1-15 days past due') --problemo

INSERT INTO PastDue VALUES  (2, 16,30, '16-30 days past due')

INSERT INTO PastDue VALUES  (3, 31,60, '31-60 days past due')

INSERT INTO PastDue VALUES  (4, 61,90, '61-90 days past due')

GO

SELECT *

FROM PastDue

WHERE 5 BETWEEN BucketLo and BucketHi

GO

SELECT   SQL_VARIANT_PROPERTY(BucketLo,'BaseType') AS 'Base Type'

FROM  PastDue

GO

As you can see, the implicit conversion did not work for varchar values of 1 and 15. Not good.  While it works fine without having to cast or convert for integers, the same cannot be said for strings. (Books Online doesn’t want you to do either, and the example here is most likely for the reason for the disclaimer on sql_variant conversions).  I had to do some digging to find this error; although I knew that text was the data type used in Access, for some reason I was thinking text as in BLOB, and that the type would be handled correctly when exported to SQL Server. I was certainly wrong. 

Lee

-----------------------------

We have it...might as well use it?  Uh, no.