blog.atwork.at

news and know-how about microsoft, technology, cloud and more.

Wenn in SQL Server sogar bei varchar(max) bei 8000 Zeichen Schluss ist...

Microsofts SQL Server ist fast in jedem Microsoft (Server) Produkt drin. Und auch mein Lieblings-Produkt. So habe ich im Laufe meines Berufslebens bereits von SQL Server 6.5 bis hin zum aktuellen SQL Server 2008 R2 viel Datenbank-Wartung durchgeführt und viele Lösungen entwickelt. Hierbei lernt man viel - und auch viele Stolpersteine!

So hatte ich vor kurzem die Anforderung, eine Reihe von SQL Server Reports neu zu generieren. Dazu habe ich mal vor einigen Jahren ein kleines Tool geschrieben, das eine XML Datei durchläuft und alle dort enthaltenen Reports mit bestimmten Parametern als PDF File persistiert - das soll weiterverwendet werden. Nun haben sich die Reports und deren Parameter geändert und eigentlich muss ich zum Neuaufruf nur das reports.xml File neu erstellen und das Tool (sozusagen per Knopfdruck) starten und alles sollte erledigt sein. Soweit so gut, das war die Vorgeschichte.

Zum Generieren des neuen XML-Files verwende ich eine einfaches, zusammengebautes Script (das ich mir zum Glück aus dem Vorjahr gemerkt habe) direkt im SQL Server 2008 R2 SQL Management Studio. Dieses durchläuft eine gejointe Tabelle und schreibt den Output in eine Textvariable @x.

Das ganze Script sieht vereinfacht etwa so aus:

declare @x varchar(max)
set @x = '<?xml version="1.0" encoding="ISO-8859-1"?>' +
char(13) + char(10) + '<reports>' + char(13) + char(10)

SELECT @x = @x +
'<report>' + char(13)+char(10)+
'<name>/app/betrieb</name>' + char(13)+char(10)+
'<param>format=PDF|id=' + convert(varchar,BET_ID) + '</param>' + char(13) + char(10)+
'<path>C:\temp\betrieb_'+ right('00' + convert(varchar,(ROW_NUMBER() OVER (ORDER BY Name1))),2) +
'.pdf</path>' + char(13) + char(10)+
'</report>' + char(13) + char(10)
FROM Betriebe LEFT OUTER JOIN Bundeslaender ON BET_BDL_ID = Bundeslaender.BDL_ID
WHERE (BetriebActive = 1)
ORDER BY Bundesland, Name1

set @x = @x + '</reports>' + char(13)+char(10)
print @x

Der so erzeugte Text in @x wird dann einfach aus dem SQL Management Studio ausgeführt (F5) und ausgegeben, für jede Tabellenzeile (bei mir etwa 30 Reports) wird ein Abschnitt "<report>" erstellt, etwa so:

sql-output

Der Text in Messages wird dann per Zwischenablage in ein Textfile kopiert - bzw. wird die Ausgabe direkt in ein File geschrieben "Results to File":

sql-to-file

Funktioniert ... fast!  Der Output wird irgendwo abgeschnitten!

sql-output-cut

Moment mal, unsere Variable @x ist doch vom Typ varchar(max)!

Damit sind doch theoretisch bis zu 2GB Zeicheninhalt möglich. Also mal nachsehen, wie lange @x tatsächlich ist:

print len(@x)

ergibt sportliche 10655 Zeichen. Die Variable beinhaltet also den tatsächlichen Inhalt
- nur wird der abgeschnitten... eine leise Vorahnung (eigentlich ein Rückblick) kommt auf...

Seit SQL Server 2005 gibt es die Aufhebung der 8000-Zeichen Grenze bei Texten vom Datentyp Zeichen: Konkret war bis SQL Server 2000 aufgrund der internen Data Page-Größen bei 4000 Zeichen für nvarchar und bei 8000 Zeichen bei varchar Schluss. Nun kann stattdessen varchar(MAX) bis 2^31-1 Bytes (also fast 2GB) verwendet werden. Damit können ab SQL 2005 auch Zeichenkettenoperationen mit längeren Texten durchgeführt werden - ohne mühsame Workarounds mit Konvertierungsfunktionen und Datentyp TEXT.

Die Ursache für das Abschneiden ist also das PRINT Statement.

PRINT schneidet bei 8000 Zeichen ab!

Print ist ja sehr praktisch. Nur hat Microsoft anscheinend vergessen, die neuen Grenzen ab SQL Server 2005 nachzuziehen? Natürlich dachte ich zuerst an die Optionen im SQL Management Studio, wo die Limits der Ausgabe eingestellt werden kann. Aber nein, das ist es nicht. Und ich könnte natürlich Zwischenschritte einlegen oder das Problem anders lösen ... aber nur weil Print die Ausgabe abschneidet alles umstellen??

Aber es gibt eine einfache Abhilfe: In diesem Blog-Artikel von Falafel Software von Adam Anderson wird eine kleine Stored Procedure "LongPrint" in nur insgesamt 26 Zeilen erstellt, welche einen langen String auf mehrere Teile á 4000 Zeichen vom Typ nvarchar aufteilt und schrittweise ausgibt:

T-SQL: Exceeding the 8000 Byte Limit of the PRINT Statement

Zur Anzeige des ganzen Scripts folgen Sie einfach dem Link oder klicken Sie auf dieses Bild:

sql-longprint

Eine einfache Lösung:

1. Einmaliges Ausführen des LongPrint() Scripts in der eigenen Datenbank und danach

2. statt im eigenen Script PRINT @x zu verwenden, einfach

exec LongPrint @x

angeben.

So klappts mit langen Strings mit mehr als 8000 (bzw. 4000) Zeichen - ohne dass die bestehende Funktionalität umgebaut werden muss!

Ein netter, kleiner Workaround bis sich Microsoft hoffentlich diesem Problem annimmt und das Limit von PRINT - vielleicht mit der nächsten SQL Server Version "Denali" (2012?) - behebt. Zwinkerndes Smiley



Loading