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
– Dan Guzman, Commented Jun 17, 2018 at 16:39
It is below 4000 characters, yes. I've tried nvarchar(max) and varchar(max), with same result. — Refi
– Refi, Commented Jun 17, 2018 at 17:01

Aaditya Sharma · Accepted Answer · 2019-07-31 05:24:24Z

2

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')

edited Jul 31, 2019 at 5:24

Aaditya Sharma

4,1703 gold badges28 silver badges38 bronze badges

answered Jul 31, 2019 at 4:02

VivC

212 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Book Of Zeus · Accepted Answer · 2021-06-04 05:20:52Z

1

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')

edited Jun 4, 2021 at 5:20

Book Of Zeus

49.9k18 gold badges176 silver badges171 bronze badges

answered Jun 3, 2021 at 18:10

Blanca Garcia

111 bronze badge

Collectives™ on Stack Overflow

Azure SQL db query external Azure SQL DB - PPDwManagedToNativeInteropException

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related