Kürzlich entstand im Rahmen der DeltaMaster-ETL-Entwicklung einem Rätsel, welches nicht erklärt werden konnte. Es ging um die dynamische Erzeugung eines SQL-Objekts aus zusammengesetzten Strings. Trotz der Nutzung des Datentyps varchar(max) wurde der String abgeschnitten. Bis dato war die Annahme, varchar(max) wäre eine sichere Bank, um das Abschneiden von Textketten zu verhindern. Weit gefehlt. Unter gewissen Umständen werden auch varchar(max)-Texte gekürzt. Die Lösung dafür ist banal, aber darauf kommen muss man erstmal. Der vorliegende Blogbeitrag spart hoffentlich allen Lesern langwieriges Knobeln.
Varchar(max) wird abgeschnitten
Schauen wir uns zunächst das erste Phänomen an. Es wurde eine Variable als varchar(max) definiert und in dieser wurde aus mehreren Einzelblöcken ein SQL-Kommando zusammengebaut. Das hat bislang auch immer einwandfrei funktioniert. Nun wurde aber einer der Einzelblöcke geringfügig erweitert, was dazu geführt hat, dass die magische 8000er-Grenze der String-Länge überschritten wurde. Das wiederum hat dazu geführt, dass das Kommando abgeschnitten wurde. Aber warum? Die Zielvariable war doch varchar(max)?! Bauen wir uns zunächst mal ein Beispiel auf, um das Verhalten nachzuvollziehen.
Es wird eine Zielvariable mit dem Datentyp varchar(max) erstellt und dieser wird ein Kommando aus mehreren einzelnen Quellvariablen zugewiesen. Anschließend lässt man sich den Text samt Länge ausgeben und führt den Befehl aus:
DECLARE @SQLCmd varchar(max)
DECLARE @SQLBlock1 varchar(6000)
DECLARE @SQLBlock2 varchar(6000)
DECLARE @SQLBlock3 varchar(6000)
--Getrennte Strings a ca. 6000 Zeichen aufbauen
SET @SQLBlock1 = replicate('SELECT 1 UNION ALL ', 315) -- 19 Zeichen String * 315 = 5.985
SET @SQLBlock2 = replicate('SELECT 2 UNION ALL ', 315)
SET @SQLBlock3 = replicate('SELECT 3 UNION ALL ', 315)
--Befehl zusammensetzen
SET @SQLCmd = @SQLBlock1 + @SQLBlock2 + @SQLBlock3 + 'SELECT 4' --Vollständige Länge: 5.985 * 3 + 8 = 17.964
--String samt Länge ausgeben
SELECT len(@SQLCmd) SQLLen, @SQLCmd SQLCmd
--Befehl ausführen
EXEC (@SQLCmd)Führt man das Beispielskript schlägt die Ausführung fehl:
Prüft man den erzeugten String sowie die Länge stellt man fest, dass der String nach 8000 Zeichen abgeschnitten wird:
Der String endet folgenermaßen:
Das kann natürlich nicht funktionieren. Die spannende Frage ist nur warum? Der String müsste doch eigentlich in die Variable “reinpassen”?!
Varchar(max) wird nicht abgeschnitten
Durchsucht man die einschlägigen Webseiten stößt man sogar bei Microsoft auf einen entsprechen-den Hinweis. Dort existiert in der Online-Dokumentation ein eigener Artikel, in dem es nur um das Verketten von Zeichenketten geht:
Dort existiert sogar ein Beispiel, welches unserem sehr ähnlich ist:
Zunächst fällt hier auf, dass das Gesamtergebnis nur eine Länge von 16.000 Zeichen aufweist (und nicht 24.000 wie es korrekt wäre). Trotz allem ist die Länge größer als 8.000 Zeichen wie in unserem Beispiel? Bei genauerer Betrachtung fällt auf, dass eine der drei Variablen selbst als varchar(max) deklariert ist, die anderen als varchar(8000). In der Zuweisung der Variablen an das Endergebnis wird dabei die alphabetische Reihenfolge vertauscht. Im beschreibenden Text zu dem Bei-spiel findet man die ersten entscheidenden Hinweise:
Da die Verkettung von links interpretiert wird, wird der Text der ersten Verkettung bei 8000 Zeichen abgeschnitten, in der zweiten Verkettung nicht?! Offensichtlich hat die Länge der Einzelblöcke also einen Einfluss auf die Länge der Zielvariable bzw. darauf, ob Text abgeschnitten wird oder nicht! Und das obwohl, dass Ziel die Länge aufnehmen könnte!
Weiter oben in den Bemerkungen des Artikels findet man dann die entscheidenden zwei Zeilen, welche die Vermutung noch einmal bestätigen:
Sprich dadurch, dass in unserem Beispiel weder die verwendeten Einzel-Code-Blöcke, noch der konstante Text größer als 8000 Zeichen ist, wird der gesamte verkettete Text abgeschnitten. Unabhängig davon, wie groß die Zielvariable ist.
Daraus folgere ich, dass es reichen müsste, eine der Einzelvariablen entsprechend zu vergrößern. Fangen wir einmal von hinten an und versuchen den konstanten Text zu vergrößern:
DECLARE @SQLCmd varchar(max)
DECLARE @SQLBlock1 varchar(6000)
DECLARE @SQLBlock2 varchar(6000)
DECLARE @SQLBlock3 varchar(6000)
--Getrennte Strings a ca. 6000 Zeichen aufbauen
SET @SQLBlock1 = replicate('SELECT 1 UNION ALL ', 315) -- 19 Zeichen String * 315 = 5.985
SET @SQLBlock2 = replicate('SELECT 2 UNION ALL ', 315)
SET @SQLBlock3 = replicate('SELECT 3 UNION ALL ', 315)
--Befehl zusammensetzen
SET @SQLCmd = @SQLBlock1 + @SQLBlock2 + @SQLBlock3 + convert(varchar(MAX), 'SELECT 4') --Vollständige Länge: 5.985 * 3 + 8 = 17.964
--String samt Länge ausgeben
SELECT len(@SQLCmd) SQLLen, @SQLCmd SQLCmd
--Befehl ausführen
EXEC (@SQLCmd)Das einzige was wir damit allerdings erreichen ist eine neue Fehlermeldung:
Ein Blick auf das Prüfstatement verrät die Ursache:
Offensichtlich wurde der erste Teil des Strings durch die Vergrößerung gar nicht verändert, lediglich das Ergänzen des letzten (vergrößerten) Blocks hat die 8000 vergrößert.
Wie wir weiter oben in der Hilfe gelesen haben wird das Statement von links interpretiert. Sprich erst werden @SQLBlock1 und @SQLBlock2 verkettet. Da diese beiden jeweils varchar(6000) sind, wird die Gesamtlänge des Statements nicht über die 8000er-Grenze vergrößert. Auch das Ergänzen der nächsten varchar(6000)-Variable @SQLBlock3 ändert daran nichts. Das Ergebnis bleibt 8000 Zeichen lang. Erst der letzte Teil schafft es den String zu vergrößern.
Ergo müssen wir möglichst weit links in der Kette mit der Vergrößerung beginnen. Daher probieren wir folgendes Statement:
DECLARE @SQLCmd varchar(max)
DECLARE @SQLBlock1 varchar(max)
DECLARE @SQLBlock2 varchar(6000)
DECLARE @SQLBlock3 varchar(6000)
--Getrennte Strings a ca. 6000 Zeichen aufbauen
SET @SQLBlock1 = replicate('SELECT 1 UNION ALL ', 315) -- 19 Zeichen String * 315 = 5.985
SET @SQLBlock2 = replicate('SELECT 2 UNION ALL ', 315)
SET @SQLBlock3 = replicate('SELECT 3 UNION ALL ', 315)
--Befehl zusammensetzen
SET @SQLCmd = @SQLBlock1 + @SQLBlock2 + @SQLBlock3 + 'SELECT 4' --Vollständige Länge: 5.985 * 3 + 8 = 17.964
--String samt Länge ausgeben
SELECT len(@SQLCmd) SQLLen, @SQLCmd SQLCmd
--Befehl ausführen
EXEC (@SQLCmd)Diese Abfrage läuft schließlich fehlerfrei und liefert das gewünschte Ergebnis:
Wie versprochen eine triviale Lösung. Nur drauf kommen muss man erstmal.
Bisher jedenfalls war mir dieses Phänomen weder bewusst, noch ist es mir in meiner mehr als 20-jährigen Beraterpraxis je untergekommen. Again what learned!
