3

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

Dharman
  • 30,962
  • 25
  • 85
  • 135
Ben Rosenzweig
  • 121
  • 1
  • 5
  • I guess the problem is length/datatype. I would create view on remote site with explicit cast `CAST(field as varchar(4000))` and try to `select from [linked_server].[projectId].[dataset].[view_name]` – Lukasz Szozda Mar 13 '18 at 15:35
  • Thanks @lad2025. The problem is that there is no varchar in BiqQuery, nor TEXT (which I found as a possible solution). – Ben Rosenzweig Mar 13 '18 at 16:05

1 Answers1

8

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

Dharman
  • 30,962
  • 25
  • 85
  • 135
Ben Rosenzweig
  • 121
  • 1
  • 5