Azure SQL db query external Azure SQL DB - PPDwManagedToNativeInteropException

Question

I have one Azure SQL server where I have several databases. I need to be able to query across these databases, and have at the moment solves this through external tables. A challange with this solution is that external tables does not support all the same data types as ordinary tables.

According to the following article the solution to incompatible data types are to use other similiar ones in the external table. https://learn.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-data-types#unsupported-data-types.

DDL for table in DB1

CREATE TABLE [dbo].[ActivityList](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Registered] [datetime] NULL,
    [RegisteredBy] [varchar](50) NULL,
    [Name] [varchar](100) NULL,
    [ak_beskrivelse] [ntext] NULL,
    [ak_aktiv] [bit] NULL,
    [ak_epost] [bit] NULL,
    [Template] [text] NULL
 CONSTRAINT [PK_ActivityList] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

DDL for external table in DB2

CREATE EXTERNAL TABLE [dbo].[NEMDBreplicaActivityList]
(
    [ID] [int] NOT NULL,
    [Registered] [datetime] NULL,
    [RegisteredBy] [varchar](50) NULL,
    [Name] [varchar](100) NULL,
    [ak_beskrivelse] [nvarchar](4000) NULL,
    [ak_aktiv] [bit] NULL,
    [ak_epost] [bit] NULL,
    [Template] [varchar](900) NULL
)
WITH (DATA_SOURCE = [DS],SCHEMA_NAME = N'dbo',OBJECT_NAME = N'ActivityList')

Querying the external table NEMDBreplicaActivityList produces the following error

Error retrieving data from server.database.windows.net.db1. The underlying error message received was: 'PdwManagedToNativeInteropException ErrorNumber: 46723, MajorCode: 467, MinorCode: 23, Severity: 16, State: 1, ErrorInfo: ak_beskrivelse, Exception of type 'Microsoft.SqlServer.DataWarehouse.Tds.PdwManagedToNativeInteropException' was thrown.'.

I have tried defining the ak_beskrivelse column as other external table legal datatypes, such as varchar, with the same result.

Sadly I'm not allowed to edit the data type of columns in the db1 table.

I assume that the error is related to the data type. Any ideas how to fix it?

Is the actual max length of `ak_beskrivelse` 4000 characters? If larger, try nvarchar(MAX). Similarly, varchar(MAX) for `Template` if over 8000 characters. — Dan Guzman, Jun 17 '18 at 16:39
It is below 4000 characters, yes. I've tried nvarchar(max) and varchar(max), with same result. — Refi, Jun 17 '18 at 17:01

score 2 · Answer 1 · edited Jul 31 '19 at 05:24

I solved a similar problem to this by creating a view over the source table which cast the text value as varchar(max), then pointed the external table to the view. So:

CREATE VIEW tmpView
AS 
SELECT CAST([Value] AS VARCHAR(MAX))
FROM [Sourcetable].
Then:
CREATE EXTERNAL TABLE [dbo].[tmpView]
(
 [Value] VARCHAR(MAX) NULL
)
WITH (DATA_SOURCE = [myDS],SCHEMA_NAME = N'dbo',OBJECT_NAME = N'tmpView')

score 1 · Answer 2 · edited Jun 04 '21 at 05:20

Creating the view and casting the text value worked perfect for me Thank you!

Created view vw_TestReport:

SELECT CAST([Report Date] AS VARCHAR(MAX)) AS [Report Date]
FROM     dbo.TestReport

And created external table from view:

CREATE EXTERNAL TABLE [dbo].[TestReport](
    [Report Date] [varchar](max) NULL
) 
WITH (DATA_SOURCE = [REFToDB],SCHEMA_NAME = N'dbo',OBJECT_NAME = N'vw_TestReport')

Azure SQL db query external Azure SQL DB - PPDwManagedToNativeInteropException

2 Answers2