Requested conversion is not supported

[alainbryden]

Version

• Driver version: 2.1.3.0
• Redshift version PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.71629
• Client Operating System: Windows Server 2019

Problem description

Upon upgrading from 1.x to 2.x driver versions, most of our queries now return the following error:

OLE DB provider "MSDASQL" for linked server "REDSHIFT" returned message "Requested conversion is not supported.".

This happens for pretty any OPENQUERY([REDSHIFT], 'SELECT * FROM sometable') which ends up containing a "TEXT" column in it somewhere.

This never happened on the 1.x version, presumably due to there being "Data Type Options" on the old version for specifying the maximum data length of LongVarChar columns:

---

Many other ODBC drivers have a history of producing similar errors. This reportedly comes from converting various types to MaxLongVarChar, but creating nvarchars larger than 8000 (which SQL Server does not support). All of these drivers have in time come to support a "Max LongVarChar" setting, or similar, which automatically fixes this issue, without users needing to manually wrap all columns in CAST(column AS NVARCHAR(8000)) in all of their queries.

Here are some examples I've seen in the wild: From Pythian:

From Snowflake:

Please configure the workaround parameter on the remote MSSQL server's ODBC driver installation, rather than the local one (where SSMS is installed): Open Regedit as an Administrator (on the MSSQL host), navigate to HKEY_LOCAL_MACHINE > Software > ODBC > ODBC.INI > Create a new String key 'default_varchar_size' and with value 4000

From Simba:

---

Can we add back something similar here, or are the old options still available as a registry or connection string setting?

[vahid110]

Hi @alainbryden Thank you for taking the time to report this issue. I appreciate your effort and will make sure it reaches the appropriate team for investigation as soon as possible.