For a client I'm trying to create an external table which connects to an Oracle database using Polybase. While executing a select on this external table I receive the following msg:
Cannot execute the query "Remote Query" against OLE DB provider "MSOLEDBSQL" for linked server "(null)". 110802;An internal DMS error occurred that caused this operation to fail. Details: Polybase Error: Failed to convert external data format to SQL Server internal format.
We came up with a temporary fix to solve this problem. Right now we are creating a view on the external table and joining the table with itself:
SELECT a.id
,b.date
FROM [STG].[table1] a
LEFT OUTER JOIN [STG].[table1] b ON a.[id] = b.[id] and b.[date] >= '0001-01-01'
Unfortunately the performance is bad for this solution because an extra join is required for every date column.
I'm looking for a suitable alternative, help is welcome.