Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

Two collation problems with external tables in SQL 2019

Below are two scripts. The first is to be run on the target server. It creates two databases with different collatons. The first a regular SC collation, the sceond a UTF8 collation. In both is created a table "simple" which is loaded with some data.

The second script is to be executed on the local server, which must have Polybase enabled. You will need to change the server name. The script creates a database with the same SC collation used on the first server and sets up this server as an external data source.

The script then attempts to create three external table. The first is created against the "simple" table in the SC database, without any COLLATE clause. This suceeds.

In the second attempt the table definition is the same, however, the collation is listed explicitly. This now fails with the collation said to be illegal. I have not conducted any thorough research, but non-SC collation appears to be accepted.

In the third attempt, the target table is in the UTF8. This fails with "The given key was not present in the dictionary.". It is noteworthy that it sometimes can take several minutes before this message appears.

---- Target server -------------------------------------------------------------
USE tempdb
go
DROP DATABASE IF EXISTS CI_AS_SC
DROP DATABASE IF EXISTS CI_AS_UTF8
go
CREATE LOGIN PollyUser WITH PASSWORD = 'nisse'
go
CREATE DATABASE CI_AS_SC COLLATE Finnish_Swedish_100_CI_AS_SC
go
USE CI_AS_SC
go
CREATE USER PollyUser
GRANT SELECT ON SCHEMA::dbo TO PollyUser
go
CREATE TABLE simple (a int NOT NULL PRIMARY KEY,
name sysname NOT NULL,
modify_date datetime2(3) NOT NULL)

INSERT simple(a, name, modify_date)
SELECT object_id, name, modify_date
FROM msdb.sys.objects
go
CREATE DATABASE CI_AS_UTF8 COLLATE Finnish_Swedish_100_CI_AS_SC_UTF8
go
USE CI_AS_UTF8
go
CREATE USER PollyUser
GRANT SELECT ON SCHEMA::dbo TO PollyUser
go
CREATE TABLE simple (a int NOT NULL PRIMARY KEY,
name varchar(256) NOT NULL,
modify_date datetime2(3) NOT NULL)

INSERT simple(a, name, modify_date)
SELECT object_id, name, modify_date
FROM msdb.sys.objects
go

--------- Local server ---------------------------------------------

USE tempdb
go
DROP DATABASE IF EXISTS Pbase
go
CREATE DATABASE Pbase COLLATE Finnish_Swedish_100_CI_AS_SC
go
USE Pbase
go
CREATE MASTER KEY ENCRYPTION BY PASSWORD = N')(FK __G"øø';
go
CREATE DATABASE SCOPED CREDENTIAL RemoteCredentials
WITH IDENTITY = 'PollyUser', Secret = 'nisse';
go
CREATE EXTERNAL DATA SOURCE RemoteSQL
WITH (
LOCATION = 'sqlserver://SEATTLE20:1433',
PUSHDOWN = ON,
CREDENTIAL = RemoteCredentials
);
go
-- This works.
CREATE EXTERNAL TABLE simple (id int NOT NULL,
name nvarchar(128) NOT NULL,
modify_date datetime2(3) NOT NULL)
WITH (LOCATION='CI_AS_SC.dbo.simple', DATA_SOURCE = RemoteSQL)

SELECT * FROM simple
go
--- This fails with illegal collation.
CREATE EXTERNAL TABLE simple2 (id int NOT NULL,
name nvarchar(128) COLLATE Finnish_Swedish_100_CI_AS_SC NOT NULL,
modify_date datetime2(3) NOT NULL)
WITH (LOCATION='CI_AS_SC.dbo.simple', DATA_SOURCE = RemoteSQL)
go
-- This fails with "The given key was not present in the dictionary."
CREATE EXTERNAL TABLE simple3 (id int NOT NULL,
name varchar(128) NOT NULL,
modify_date datetime2(3) NOT NULL)
WITH (LOCATION='CI_AS_UTF8.dbo.simple', DATA_SOURCE = RemoteSQL)

1 vote
Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)

We’ll send you updates on this idea

Erland Sommarskog shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

0 comments

Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)
Submitting...

Feedback and Knowledge Base