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

1

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?

2

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

1 Like
3

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

Thanks,

Eric

4

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

5

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.

6

@paulmdrumm Currently Dremio does not support SQL Server Linked Server

7

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’) ;

8

Is there any plan to implement this in the future? Having access to Dremio from SQL Server without having to route through a middle-tier would be extremely helpful.

9

@gray Have you tried External query where you can pass native functions and Dremio will not parse those

10

Have you tried External query where you can pass native functions and Dremio will not parse those

@balaji.ramaswamy, Ttere’s no issue with using external query to get data from SQL Server into Dremio, but that doesn’t address the issue of not being able to use a linked server (or any ROWSET constructs, as mentioned earlier in the thread by @paulmdrumm ) to query Dremio from within SQL Server. Currently, it looks like the only option available is something like PowerShell or SSIS to get data out of Dremio and into SQL Server.

As an example. I have a table in one of my SQL Server sources with a schema like this and populated with dummy data:

CREATE TABLE DremioTypeTest
(
	[IntCol] INT
	,[BitCol] BIT
	,[DecimalCol] DECIMAL(10,3)
	,[CharCol] CHAR(100)
	,[VarCharCol] VARCHAR(100)
	,[TextCol] TEXT
	,[DateCol] DATE
	,[DateTimeCol] DATETIME
	,[TimeCol] TIME
	,[UIDCol] UNIQUEIDENTIFIER
)

I’ve saved this as a view in Dremio and it displays without issue, as expected:

image
image1162×307 52.1 KB

In PowerShell, querying the view in Dremio is also fine (ignore the UIDCol displayed value - the function represented it incorrectly, but at least data is there)

image
image733×202 7.04 KB

In SQL Server, using OPENQUERY to query each column of the view in Dremio via LinkedServer shows that we can successfully return all the data types except the ones that have alphabetical characters in them. Instead we get the error

OLE DB provider “MSDASQL” for linked server “DREMIOCLOUD” returned message “Requested conversion is not supported.”.

SELECT * FROM OPENQUERY([DREMIOCLOUD],'select IntCol from DBA.DremioTypeTest limit 1')
go	    
SELECT * FROM OPENQUERY([DREMIOCLOUD],'select BitCol from dba.DremioTypeTest limit 1')
go	    
SELECT * FROM OPENQUERY([DREMIOCLOUD],'select DecimalCol from dba.DremioTypeTest limit 1')
go	    
SELECT * FROM OPENQUERY([DREMIOCLOUD],'select CharCol from dba.DremioTypeTest limit 1')
go	    
SELECT * FROM OPENQUERY([DREMIOCLOUD],'select VarCharCol from dba.DremioTypeTest limit 1')
go	    
SELECT * FROM OPENQUERY([DREMIOCLOUD],'select TextCol from dba.DremioTypeTest limit 1')
go	    
SELECT * FROM OPENQUERY([DREMIOCLOUD],'select DateCol from dba.DremioTypeTest limit 1')
go	    
SELECT * FROM OPENQUERY([DREMIOCLOUD],'select DateTimeCol from dba.DremioTypeTest limit 1')
go	    
SELECT * FROM OPENQUERY([DREMIOCLOUD],'select TimeCol from dba.DremioTypeTest limit 1')
go	    
SELECT * FROM OPENQUERY([DREMIOCLOUD],'select UIDCol from dba.DremioTypeTest limit 1')

image

11

Has there been any further consideration of linked server support?