Collation-Konflikt führt zu (mir) unerklärlichen Ergebnissen

Question

Hallo,

ich habe eine Fremddatenbank (Standardsortierung SQL_Latin1_CP1_CI_AS) und meine Datenbank (Collation Latin1_CI_AS). Ich benötige aus einer Tabelle der Fremddatenbank alle Datensätze, die ich noch nicht in meiner Datenbank habe. Das Feld, auf das ich zugreifen kann, ist in der Fremddatenbank ein VARCHAR(20), bei mir ein NVARCHAR(20).

Rechtemäßig habe ich eine View erhalten, die mir die Daten ausspuckt, auf die ich selektieren kann.

Ich habe also folgenden SQL-Ausdruck:

SELECT Schlüsselfeld
FROM v_ViewFremddatenbank
WHERE Schlüsselfeld NOT IN (SELECT MeinSchlüsselfeld FROM MeineDatenbank.MeineTabelle)

Das liefert: Ein Sortierungskonflikt zwischen "Latin1_General_CI_AS" und "SQL_Latin1_General_CP1_CI_AS" im equal to-Vorgang kann nicht aufgelöst werden.

Also "verbessere" ich nach ein wenig googeln

SELECT Schlüsselfeld
FROM v_ViewFremddatenbank
WHERE Schlüsselfeld NOT IN (SELECT MeinSchlüsselfeld COLLATE SQL_Latin1_General_CP1_CI_AS FROM MeineDatenbank.MeineTabelle)

und erhalte eine leere Menge zurück (mehrfach angeschaut, Werte manuell in eine Liste gepackt und auf IN ('str1', 'str2',...) getestet), obwohl ich Datensätze zurückbekommen müsste. Teste ich auf "IN" (lasse also das NOT in der Where-Bedingung weg, dann kommen genau die Datensätze, die ich erwarte.

Interessanterweise funktioniert

SELECT Schlüsselfeld
FROM v_ViewFremddatenbank
WHERE Schlüsselfeld NOT IN (SELECT MeinSchlüsselfeld COLLATE Latin1_General_CI_AS FROM MeineDatenbank.MeineTabelle)

auch ohne Fehlermeldung, logischerweise auch leer...

Aber

SELECT Schlüsselfeld COLLATE Latin1_General_CI_AS
FROM v_ViewFremddatenbank
WHERE Schlüsselfeld NOT IN (SELECT MeinSchlüsselfeld  FROM MeineDatenbank.MeineTabelle)

liefert den ursprünglichen Fehler zurück.

NACHTRAG 15:08: Die View ist ein OpenQuery-Konstrukt, also
CREATE v_ViewFremddatenbank AS
SELECT Schlüsselfeld,... FROM
OPENQUERY (Verbindungsserver, 'SELECT Felder aus Tabellen')

um dieses Konstrukt komme ich leider nicht herum.

Und nun bin ich ratlos. Ich habe wohl die Anwendung der COLLATE-Funktion trotz alles Lesens und Googelns nicht verstanden. Ich hoffe, jemand kann mir helfen.

Danke schon mal im Voraus,
Wolfgang

NACHTRAG 15:50: Mittlerweile wurde das abgeändert zu:
CREATE v_ViewFremddatenbank AS
SELECT s COLLATE Latin1_General_CI_AS AS Schlüsselfeld,... FROM
OPENQUERY (Verbindungsserver, 'SELECT Schlüsselfeld as s,...')

Nun wirft die allererste Form der Abfrage (ohne ein COLLATE) keinen Fehler mehr, liefert bei einer Abfrage ohne Where-Bedingung 230 Datensätze, bei einer Abfrage Where Schlüsselfeld IN (Select...) 160 und bei der Abfrage Where NOT IN (SELECT...) 0 (keine, nix!) Zeile

Ich geh jetzt ins Wochenende. Mag nicht mehr!

Answer 1

Hallo AWombi,

versuche es mal mit Lösung (2), sonst Lösung (3); bei der Lösung (3) ggfs. die Zeile:

where #t2.id_t1 is null

auskommentieren, damit Du siehst, welche Datensätze nicht angezeigt werden.

--Alle aus t1, für die kein Eintrag in t2 existiert
--Bei EXISTS gibt's keine Probleme mit NULL


CREATE TABLE #t1 (ID INT, Name varchar (20))

CREATE TABLE #t2 (id_t1 INT, Status varchar (20))

INSERT INTO #t1 SELECT 1, 'Honig'
INSERT INTO #t1 SELECT 2, 'Zucker'
INSERT INTO #t1 SELECT 3, 'Milch'
INSERT INTO #t1 SELECT 4, 'Senf'
INSERT INTO #t1 SELECT 5, 'Salat'

INSERT INTO #t2 SELECT 3, 'Lieferung'
INSERT INTO #t2 SELECT 5, 'Nachbestellung'


GO

select #t1.id, Name
from #t1

select #t2.id_t1, Status
from #t2

GO

select #t1.id, Name AS [Lösung 1]
from #t1
where #t1.id not in 
			(select id_t1 
			from #t2 
			where #t1.id is not null)

GO

select #t1.id, Name AS [Lösung 2]
from #t1
where NOT EXISTS
		(select * from #t2
		where id_t1 = id)

GO

select #t1.id, Name AS [Lösung 3]
from #t1 
	left join #t2 ON #t1.id = #t2.id_t1
	where #t2.id_t1 is null

Schönen Abend.

Answer 2

NVARCHAR ist Unicode (UTF-8) und VARCHAR ist einfach ANSI (8-Bit-Bytes).
Eine Collation dient i.w. der caseinsensitiven Sortierung und Vergleiche für verschiedene Sprachräume im 8-Bit-Raum. Bei UTF-8 (NVARCHAR) ist diese normalerweise nicht mehr erforderlich-
Caste das Quellfeld also ebenso in NVARCHAR:

SELECT Schlüsselfeld
FROM v_ViewFremddatenbank
WHERE cast(Schlüsselfeld as nvarchar(20)) NOT IN (SELECT MeinSchlüsselfeld FROM MeineDatenbank.MeineTabelle)

An Stelle von "NOT IN" könnte noch ein "NOT EXISTS" ggf. performanter sein (bei mehreren 1000den Sätzen).

Answer 3

bei einer Abfrage Where Schlüsselfeld IN (Select...) 160 und bei der Abfrage Where NOT IN (SELECT...) 0 (keine, nix!) Zeile

Hallo,

Eine NOT IN hat eine Stolperfall, die man kennen muss: Liefert die Unterabfrage einen NULL Wert zurück, erhält man nie ein Ergebnis, denn ein Vergleich auf NULL endet in einem undefiniertem Ergebnis und so im Zweifelsfall auf False; siehe NULL and UNKNOWN (Transact-SQL)

Beispiel, das man auf jeder Datenbank ausführen kann:

-- Liefert ein Ergebnis
select *
from sys.tables as tbl
where not tbl.object_id in (select 1);

-- Liefert kein Ergebnis
select *
from sys.tables as tbl
where not tbl.object_id in (select 1 UNION ALL SELECT NULL);

Du musst also vorhandene NULL Werte ausfiltern, damit es funktioniert:

SELECT Schlüsselfeld 
FROM v_ViewFremddatenbank
WHERE Schlüsselfeld NOT IN (SELECT MeinSchlüsselfeld FROM MeineDatenbank.MeineTabelle WHERE NOT MeinSchlüsselfeld IS NULL)

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 4

Bei "Not Exists" existiert das Problem erst gar nicht.

Answer 5

Hallo,

ich bin erst heut wieder ins Büro gekommen, daher die späte Rückmeldung. Danke für Eure Mühe.

Die Sache mit den NULL-Werten hat eine ganz große Chance... Ich komme vor morgen nicht zum Ausprobieren, melde mich aber sicher nochmal zurück!

Gruß
Wolfgang

Die Nullwerte waren es!

Und ich habe die Werte jetzt ordentlich nach NVARCHAR gecasted, damit das einfach in trockenen Tüchern ist.

Danke Euch allen noch einmal,

Wolfgang