SQL Server PolyBase External table between databases on the same instance, error "user defined column is not compatible"

Question

I have two databases on the same instance of SQL Server 2019. Where I am creating an external table in one database to that on another.

The table names and columns (types) are supposed to be identitical, yet I get an error reporting that the columns in one table are not comptible with columns in the other on all yypes. Below is screen shot of souce table:

Here is the create external table in the other database:

Here is the reultant error message:

Msg 105083, Level 16, State 1, Line 19

105083;The following columns in the user defined schema are incompatible with the external table schema for table 'ClientAlias': user defined column: ([URNumber] VARCHAR(7) NOT NULL) is not compatible with the type of detected external table column: ([URNumber] NVARCHAR(7) COLLATE Latin1_General_CI_AS NOT NULL), user defined column: ([AliasNo] VARCHAR(1) NOT NULL) is not compatible with the type of detected external table column: ([AliasNo] NVARCHAR(1) COLLATE Latin1_General_CI_AS NOT NULL), user defined column: ([AliasSurname] VARCHAR(50)) is not compatible with the type of detected external table column: ([AliasSurname] NVARCHAR(50) COLLATE Latin1_General_CI_AS), user defined column: ([AliasChristianNames] VARCHAR(50)) is not compatible with the type of detected external table column: ([AliasChristianNames] NVARCHAR(50) COLLATE Latin1_General_CI_AS), user defined column: ([AlphaSearch] VARCHAR(5)) is not compatible with the type of detected external table column: ([AlphaSearch] NVARCHAR(5) COLLATE Latin1_General_CI_AS), user defined column: ([LastModifyId] VARCHAR(6)) is not compatible with the type of detected external table column: ([LastModifyId] NVARCHAR(6) COLLATE Latin1_General_CI_AS), user defined column: ([Sex] VARCHAR(1)) is not compatible with the type of detected external table column: ([Sex] NVARCHAR(1) COLLATE Latin1_General_CI_AS), user defined column: ([DateOfBirth] DATE) is not compatible with the type of detected external table column: ([DateOfBirth] NVARCHAR(10) COLLATE Latin1_General_CI_AS), user defined column: ([DateOfLastMod] DATE) is not compatible with the type of detected external table column: ([DateOfLastMod] NVARCHAR(10) COLLATE Latin1_General_CI_AS), user defined column: ([TimeMod] TIME(7)) is not compatible with the type of detected external table column: ([TimeMod] NVARCHAR(16) COLLATE Latin1_General_CI_AS), user defined column: ([TermMod] VARCHAR(3)) is not compatible with the type of detected external table column: ([TermMod] NVARCHAR(3) COLLATE Latin1_General_CI_AS), user defined column: ([Title] VARCHAR(4)) is not compatible with the type of detected external table column: ([Title] NVARCHAR(4) COLLATE Latin1_General_CI_AS). The detected external table schema is: ([URNumber] NVARCHAR(7) COLLATE Latin1_General_CI_AS NOT NULL, [AliasNo] NVARCHAR(1) COLLATE Latin1_General_CI_AS NOT NULL, [AliasSurname] NVARCHAR(50) COLLATE Latin1_General_CI_AS, [AliasChristianNames] NVARCHAR(50) COLLATE Latin1_General_CI_AS, [AlphaSearch] NVARCHAR(5) COLLATE Latin1_General_CI_AS, [LastModifyId] NVARCHAR(6) COLLATE Latin1_General_CI_AS, [Sex] NVARCHAR(1) COLLATE Latin1_General_CI_AS, [DateOfBirth] NVARCHAR(10) COLLATE Latin1_General_CI_AS, [DateOfLastMod] NVARCHAR(10) COLLATE Latin1_General_CI_AS, [TimeMod] NVARCHAR(16) COLLATE Latin1_General_CI_AS, [TermMod] NVARCHAR(3) COLLATE Latin1_General_CI_AS, [Title] NVARCHAR(4) COLLATE Latin1_General_CI_AS).

If I change all columns data type to nvarchar, the external table imports.

Note: Collation setting i the same on both database tables to Latin1 General CI AS

Answer 1

This is not an error, in Polybase you need to define the collation when creating the external columns, it's not enough to say they have the same datatype. Just follow the informational message while creating your external table and you'll be good to go:

Wrong: [URNumber] VARCHAR(7) NOT NULL

Correct: [URNumber] NVARCHAR(7) COLLATE Latin1_General_CI_AS NOT NULL

Answer 2

I've managed to wreck my Polybase installation, so I can't test. But it seems that the remote table columns are exposed as as nvarchar rather than varchar. I guess this is an artefact of the Polybase service.

Out of curiousity, why do you set up an external table for table in another database on the same server? Isn't making things more complicated than needed?