PyMsSQL reading bad data for sql_variant columns

Question

I am using pymssql for Python 2.7 with MS SQL Driver. While running some queries on tables having sql_variant column, I am getting bad data. When I cast data, I am able to get correct values. Is there any way I can skip explicit CAST.

Answer 1

Was fighting the same issue for some time now, but I haven't found any fix yet. CAST seems the only thing to work, so I had to re-write my SQLs into something like:

sql = 'SELECT *, CAST (sql_varian_field AS BIGINT) as my_field_fixed FROM myTable'

and just refering to the last column there.

Although pymssql makes it possible to add some sql when connecting in conn_properties:

pymssql.connect(server='.', user='', password='', database='', timeout=0, login_timeout=60, charset='UTF-8', as_dict=False, host='', appname=None, port='1433', conn_properties, autocommit=False, tds_version='7.1')

Is there any SQL specialist that can answer the following question: is there any simple SQL command that would make the DB handle all sql_variant fields as some pre-defined types for entire session? Something that could be passes as conn_propeties any time a pymssql.connect is called?