SQL Server Linked Server ("Requested conversion is not supported.")

I have created a LinkedServer in SQL Server using Dremio Connector (ODBC) v1.3.19.1052.

When querying Dremio via OPENQUERY, anytime it tries to bring data back from any “Char” Column, I get this error:
OLE DB provider “MSDASQL” for linked server “DREMIO” returned message “Requested conversion is not supported.”.
Msg 7341, Level 16, State 2, Line 1
Cannot get the current row value of column “[MSDASQL].SHIP_CODE” from OLE DB provider “MSDASQL” for linked server “DREMIO”.

When I query any “NUMERIC” columns, I get the data back immediately, without any issues.

Additionally when changing the setting in:
Advanced Properties of the Dremio Connector DSN Setup:
From StringColumnLength=65535
To: StringColumnLength=4000

I get this same error from May of last year (SQL Server Linked Server)
The OLE DB provider “MSDASQL” for linked server “DREMIO” supplied inconsistent metadata for a column. The column “Category” (compile-time ordinal 1) of object “SELECT “Category” FROM “Sample Data”.“SF Crime Data” limit 100” was reported to have a “DBCOLUMNFLAGS_ISLONG” of 128 at compile time and 0 at run time.

Can you please provide assistance?

Officially, Dremio does not support linked servers today, but is something we are considering for our roadmap.

1 Like

Hi. Any update on this? We’d like to use Dremio as a Linked Server but are running into the same issue.

Thanks,

Eric

Does anyone know if/when this feature will be available in community and/or enterprise? Thanks in advance

Its something to do with how openrowset(linked server) handles the 64k string data fields.
Powershell handles it fine and the data type of those fields shows as a system.object instead of a system.valueobject. If you use a sql agent job and use the SQLPS subsystem steps you can access the data. I have tried converting the data using substring on the Dremio side to no avail.

@paulmdrumm Currently Dremio does not support SQL Server Linked Server

Not using a Linked Server per se. Using the OPENROWSET construct. It will bring back decimal, dates, int, etc, etc. but not strings. I can bring back the length of the string but not the string itself. I think this is an internal Microsoft code issue as to how it handles string data with your driver or vice versa. I have no problems with SQL powershell (SQLPS) or normal powershell. Just the T-SQL ROWSET constructs ( OPENDATASOURCE , OPENQUERY , or OPENROWSET).

I can specify individual columns with data types other than string and it works fine.

SELECT *
FROM OPENROWSET(N’MSDASQL’,N’DRIVER={Dremio Connector};ConnectionType=Direct;AuthenticationType=Plain;UID=MyUsername;PWD=MyPassword;DATABASE=DREMIO;HOST=MyServer;port=31010;DISABLECERTIFICATEVERIFICATION=0;DISABLEHOSTVERIFICATION=0;SSL=1;TLSProtocol=tlsv12;UseSystemTrustStore=1;CastAnyToVarchar=true;ConvertToCast=true;StringColumnLength=1000;’,
‘SELECT * FROM “DREMIO”.“DataSet”.“Table” LIMIT 10’) ;