"Requested conversion is not supported" when selecting bigquery table using linked server is SQL Server

Question

I'm trying to select data from Bigquery to SQL Server, using a linked server with ODBC with Simba driver.

Environment:

• Microsoft SQL Server 2012 (SP1) - 64 bit
• Simba Driver 64 bit

Some datatypes are returned ok (INTEGER, TIMESTAMP, BOOLEAN) but STRING does not get returned.

The error is:

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

And this is the query:

select <column_name> 
from [linked_server].[projectId].[dataset].[table]

I tried configuring the Server Options~> Collation Compatible to True, but still the same. like suggested here

Answer 1

When creating the ODBC connection, under Advanced Options, there is a field named "Default String Column Length". MSSQL can't handle over 8000 characters but the initial value over there is 65535. Changing it to 4000, for example, solved the issue.

Advanced Options