SQLCMD und BCP liefern invalide XML-Dokumente

In einem Projekt sammele ich per SQLCMD Daten und speichere diese in XML-Dateien. Mit SQL Server 2005, 2008 und 2008 R2 funktioniert dies einwandfrei.
Ab SQL Server 2012 jedoch speichert SQLCMD nicht immer ein valides XML-Dokument.

Der Grund sind Zeilenumbrüche. Die XML-Datei enthält nach jedem 2034. Zeichen einen Zeilenumbruch.
Fällt auch nur einer der Zeilenumbrüche in einen Wert oder einen Tag, ist das XML-Dokument nicht mehr valide.

Um dieses Dilemma nachvollziehen zu können, beginnen wir mit einem Skript, dass eine Datenbank wie auch eine Beispieltabelle namens xmlKunden anlegt und diese mit 25.000 Datensätzen füllt.

— Datenbank anlegen
CREATE DATABASE xmlTest;
GO
USE
xmlTest;
GO
— Tabelle und Daten anlegen
SET NOCOUNT ON;
— Variablen zum Erstellen von Daten
DECLARE @intZaehler int = 0, @strZaehler nvarchar(19)
;
— Bereits bestehende Tabelle löschen
IF Object_ID(‚xmlKunden‘) Is Not Null
BEGIN
             DROP TABLE dbo.xmlKunden;
END
— Tabelle erstellen
CREATE TABLE dbo.xmlKunden
(
             ID int IDENTITY(1,1),
             Matchcode nvarchar(8),
             Firma nvarchar(255),
             Strasse nvarchar(256),
             Plz nvarchar(8),
             Ort nvarchar(256)
);
— Tabelle mit 25.000 Datensätzen füllen
WHILE @intZaehler < 25000
BEGIN
             SET @intZaehler = @intZaehler + 1;
             SET @strZaehler = CAST(@intZaehler As nvarchar(19));
             INSERT INTO dbo.xmlKunden (Matchcode, Firma, Strasse, Plz, Ort)
                      VALUES (‚KD‘ + @strZaehler, ‚Firma‘ + @strZaehler,
                                    ‚Strasse‘ + @strZaehler, ‚P-‚ + @strZaehler, ‚Ort‘ + @strZaehler);
END
GO

Der Inhalt der Beispieltabelle soll per SQLCMD als XML-Dokument exportiert werden. Hierzu benötigen wir folgendes Skript:

SET NOCOUNT ON;
:
XML ON
SELECT
CONVERT(nvarchar(23),Getdate(), 127) As Datum,
             (SELECT           ID, Matchcode, Firma, Strasse, Plz, Ort
              FROM               xmlTest.dbo.xmlKunden Kunde
               FOR XML AUTO, TYPE)
FOR XML RAW (‚Kunden‘);

Die Datenermittlung per FOR XML liefert ein XML-Dokument, dass durch den Zusatz :XML ON als XML-Datenstrom ausgegeben wird.
Der Zusatz :XML ON funktioniert nur mit SQLCMD. Das SQL Server Management Studio quittiert die Verwendung von :XML ON mit einer Fehlermeldung.

Die Skriptdatei speichern wir als xmlSelectKunden.sql und starten dann in der Eingabeaufforderung den Datenexport mit folgendem Befehl:

SQLCMD -S <SqlServer> -i „D:\xmlSelectKunden.sql“ -o „D:\Kunden.xml“

Das Ergebnis ist ein XML-Dokument mit Zeilenumbrüchen. Die Statuszeile zeigt den Zeilenumbruch an der 2034. Stelle.

Dieses Verhalten ist netterweise bei Microsoft Connect unter dem Eintrag http://connect.microsoft.com/SQLServer/feedback/details/786004/sqlcmd-with-xml-on-break-lines-on-large-output beschrieben.
Der Eintrag bezieht sich zwar nur auf den Export per SQLCMD mit der Anweisung :XML ON, das Verhalten gilt jedoch ebenso für den Export eines XML-Dokuments per BCP. Mehr zu BCP folgt weiter unten.

Microsoft Connect hat den Eintrag bereits geschlossen und als nicht lösbar gekennzeichnet.
Es wird lediglich auf die dort aufgeführten Workarounds hingewiesen. Zum heutigen Zeitpunkt sind dies nur zwei.

Ein Workaround empfiehlt die Installation von SQLCMD in der Version vom SQL Server 2008 R2 auf SQL Server ab der Version 2012.
Dies hätte ein inhomogenes System zur Folge, was wohl in den meisten Fällen vom Administrator nicht toleriert wird.

Der zweite Workaround empfiehlt, das Ergebnis der Datenermittlung zunächst in einer Variablen vom Datentyp nvarchar(max) zu speichern und anschließend den Wert der Variablen per SELECT auszugeben.

Ok, dann ändern wir halt das Skript …

SET NOCOUNT ON;
DECLARE @xml As nvarchar(max);
SET @xml = (SELECT CONVERT(nvarchar(23),Getdate(), 127) As Datum,
                                (SELECT   ID, Matchcode, Firma, Strasse, Plz, Ort
                                       FROM     xmlTest.dbo.xmlKunden Kunde
                                  FOR XML AUTO, TYPE)
                    FOR XML RAW (‚Kunden‘));
:
XML ON
SELECT
@Xml As Ausgabe;

… und führen es erneut aus.

SQLCMD -S <SqlServer> -i „D:\xmlSelectKunden.sql“ -o „D:\Kunden.xml“

Und siehe da “ die XML-Datei enthält nun keine unerwünschten Zeilenumbrüche.
Dafür ist sie nun auf 2.049 Zeichen begrenzt. Es bleibt also bei einem nicht validen XML-Dokument.

Vielleicht hilft ja eine Anpassung des Workarounds. Wie sieht das Ergebnis aus, wenn der Export ohne den Zusatz :XML ON erfolgt?
Schließlich geben wir aktuell per SELECT lediglich eine Zeichenfolge aus.

Das lässt sich schnell herausfinden: Die Zeile mit :XML ON auskommentieren, das Skript speichern und per SQLCMD ein weiteres Mal ausführen.

SET NOCOUNT ON;
DECLARE @xml As nvarchar(max);
SET @xml = (SELECT CONVERT(nvarchar(23),Getdate(), 127) As Datum,
                                (SELECT   ID, Matchcode, Firma, Strasse, Plz, Ort
                                       FROM     xmlTest.dbo.xmlKunden Kunde
                                  FOR XML AUTO, TYPE)
                    FOR XML RAW (‚Kunden‘));
–:XML ON
SELECT @Xml As Ausgabe;

SQLCMD -S <SqlServer> -i „D:\xmlSelectKunden.sql“ -o „D:\Kunden.xml“

Mit diesem Skript liefert SQLCMD eine einfache Zeichenfolge. Das Ergebnis ist somit keine XML-Datei mehr, sondern eine Textdatei.
Mit einigen Einschränkungen, denn zum einen enthält die Ausgabe eine Kopfzeile und zum anderen ist die Breite der jeweiligen Spalten auf 256 Zeichen begrenzt.

Die Textdatei kann auch eine Informationsmeldung über einen Datenbankwechsel enthalten, wenn dieser im Skript mit dem USE-Befehl erfolgt.
Dies lässt sich vermeiden, wenn die Tabelle mit dem vollqualifizierten Namen bestehend aus Datenbank, Schema und Tabellenname angesprochen wird.

Die Ausgabe der Kopfzeile wie auch die Begrenzung der Ausgabe auf 256 Zeichen kann ebenfalls vermieden werden.
Hierfür gibt es die beiden folgenden Parameter:

-h-1         vermeidet die Ausgabe der Kopfzeile
-y0         erweitert die maximale Größe einer Spalte auf 1 MB

Die Grenze von 1 MB ist nicht fix. Sie lässt sich mit einer Zahl beliebig festlegen. Der Wert 0 steht hier für den maximalen Wert “ und der liegt nun mal bei 1 MB.

Speichern wir dieses Ergebnis nun in einer Datei mit der Erweiterung xml, erhalten wir ein valides XML-Dokument “ vorausgesetzt der Inhalt ist nicht größer als 1 MB.
Um dies zu testen, schränken wir die Datenermittlung per TOP-Klausel auf 10 Datensätze ein.

SET NOCOUNT ON;
DECLARE @xml As nvarchar(max);
SET @xml = (SELECT CONVERT(nvarchar(23),Getdate(), 127) As Datum,
                                (SELECT   TOP 10 ID, Matchcode, Firma, Strasse, Plz, Ort
                                       FROM     xmlTest.dbo.xmlKunden Kunde
                                  FOR XML AUTO, TYPE)
                    FOR XML RAW (‚Kunden‘));
–:XML ON
SELECT @Xml As Ausgabe;

Nachdem wir das Skript gespeichert haben, ergänzen wir den Aufruf von SQLCMD mit den Parametern und starten den Datenexport.

SQLCMD -S <SqlServer> -i „D:\xmlSelectKunden.sql“ -o „D:\Kunden.xml“ -h-1 -y0

Das Ergebnis ist ein valides XML-Dokument.

Entfernen wir im Skript die TOP-Klausel, erhalten wir ein Ergebnis von mehr als 1 MB.
Dann wird die Ausgabe einfach abgeschnitten “ und schon haben wir wieder kein valides XML-Dokument.

Vielleicht liegt es ja am Datentyp der Variablen, die das Ergebnis der Datenermittlung speichert. Denn wir wollten ja eigentlich ein XML-Dokument und keine Zeichenfolge.
Ändern wir doch einfach mal den Datentyp der Variablen von nachvar(max) auf xml.

Das Ergebnis ist das gleiche. Die Ausgabe ist auf 1 MB begrenzt. Daran ändert sich auch nichts, wenn das Skript wieder mit dem Zusatz :XML ON ausgeführt wird.
Der Grund liegt in der maximal möglichen Breite einer Spalte.

Als Alternative zu SQLCMD ist vielleicht BCP eine Hilfe. Da sich mit BCP jedoch keine Skripte ausführen lassen, brauchen wir für die Datenermittlung eine Sicht, eine Gespeicherte Prozedur oder eine Tabellenwertfunktion. Versuchen wir es mit einer Gespeicherten Prozedur.

USE xmlTest;
GO
CREATE
PROC dbo.pExportKundenXml
AS
SET
NOCOUNT ON;
SELECT CONVERT(nvarchar(23),Getdate(), 127) As Datum,
             (SELECT           ID, Matchcode, Firma, Strasse, Plz, Ort
              FROM               xmlTest.dbo.xmlKunden Kunde
               FOR XML AUTO, TYPE)
FOR XML RAW (‚Kunden‘);

In der Eingabeaufforderung starten wir dann den Datenexport per BCP mit diesen Parametern:

BCP xmlTest.dbo.pExportKundenXml queryout „D:\Kunden.xml“ -S <SqlServer> -T -x „c

Das Ergebnis ist ernüchternd. Die XML-Datei enthält wieder die unerwünschten Zeilenumbrüche.

Naja, dann nutzen wir doch den Workaround vom SQLCMD und schreiben das Ergebnis zunächst in eine Variable vom Datentyp nvarchar(max), um diese per SELECT auszugeben.

USE xmlTest;
GO
ALTER
PROC dbo.pExportKundenXml
AS
SET
NOCOUNT ON;
DECLARE @xml As nvarchar(max);
SET @xml = (SELECT ‚Datum = ‚ + CONVERT(nvarchar(23),Getdate(), 127) As Datum,
                                (SELECT   ID, Matchcode, Firma, Strasse, Plz, Ort
                                       FROM     xmlTest.dbo.xmlKunden Kunde
                                  FOR XML AUTO, TYPE)
                    FOR XML RAW (‚Kunden‘));
SELECT @Xml As Ausgabe;

Starten wir also den Datenexport per BCP ein weiteres Mal.

BCP xmlTest.dbo.pExportKundenXml queryout „D:\Kunden.xml“ -S <SqlServer> -T -x „c

Fantastisch “ endlich haben wir eine XML-Datei ohne die lästigen Zeilenumbrüche und somit ein valides XML-Dokument.
Sogar die Ausgabe ist nicht auf 1 MB begrenzt.

Der für SQLCMD beschriebene Workaround liefert beim Datenexport per BCP das erwartete Ergebnis.
Dabei ist es gleich, ob die Variable vom Datentyp xml oder nvarchar(max) ist.

Der Nachteil dieser Variante liegt jedoch darin, dass wir an einer Gespeicherten Prozedur oder einer Tabellenwertfunktion mit mehreren Anweisungen nicht vorbeikommen. Nun kann es ja durchaus sein, dass das Erstellen eigener Datenbankobjekte nicht erwünscht ist. In diesem Fall ist der Datenexport per BCP nicht möglich. Also schauen wir nochmal nach einer Lösung per SQLCMD.

Bei den bisherigen Datenexporten per SQLCMD wird das XML-Dokument entweder als Datenstrom oder als Zeichenfolge in einer einzelnen Spalte ausgegeben. Beim Datenstrom per :XML ON erhalten wir die Zeilenumbrüche und bei der Ausgabe in einer Spalte ist diese auf 1 MB begrenzt.

Die Begrenzung der Ausgabe auf 1 MB gilt jedoch nur für eine Spalte einer Zeile.
Warum also das Ergebnis nicht auf mehrere Zeilen verteilen?

Schreiben wir doch einfach die einzelnen Datensätze vom Ergebnis nicht per FOR XML in ein XML-Dokument, sondern zeilenweise als Zeichenfolgen in eine Tabelle. Diese Datensätze ergänzen wir mit weiteren Datensätzen, die das Start- und Ende-Tag des XML-Dokuments enthalten. Somit ergibt der Inhalt der Tabelle das eigentliche XML-Dokument.

Das Ergebnis könnte jedoch Zeichen enthalten, die für ein XML-Dokument reserviert sind. Hierzu gehören neben dem Hochkomma und dem Anführungszeichen auch die Zeichen &, < und >. Um nicht mühsam diese Zeichen per REPLACE mit den Maskierungen wie ‚“‚ ersetzen zu müssen, nutzen wir einfach wieder FOR XML für die Datenermittlung. Auf diese Weise erhalten wir ein valides XML-Dokument.

Das XML-Dokument speichern wir in einer Variable vom Datentyp xml und lesen dann die einzelnen Zeilen per XQuery aus. Dabei konvertieren wir jede einzelne Zeile in eine Zeichenfolge. Hinzu kommt eine weitere Spalte namens Satzart, die den Wert ‚D‘ für „Daten“ erhält.

Die so aufbereiteten Zeilen schreiben wir per SELECT INTO in eine Tabelle.
SELECT INTO wird an dieser Stelle nicht ohne Grund verwendet: Es ist am schnellsten.

Anschließend ergänzen wir die erstellte Tabelle mit einer weiteren Spalte namens „EintragID“. Diese Spalte erhält die IDENTITY-Funktion und vergibt somit den einzelnen Datensätzen eine fortlaufende Nummer.

Nun fehlen noch die Einträge für die Start- und Ende-Tags des XML-Dokuments.
Das Start-Tag schreiben wir mit der Satzart „A“ für „Anfang“ in die Tabelle und das Ende-Tag mit der Satzart „E“ für „Ende“.

Die Satzart dient lediglich zur Sortierung der Ausgabe, denn die folgt per ORDER BY nach den Spalten „Satzart“ und „EintragID“.
Durch diese Sortierung sind die Start-Tags am Anfang (Satzart „A“), gefolgt von den Daten (Satzart „D“) und dem Ende-Tag (Satzart „E“).

SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;

— XML-Variable
DECLARE @xmlErgebnis xml;

— Ergebnis als XML aufbereiten
SET @xmlErgebnis = (SELECT ID, Matchcode, Firma, Strasse, Plz, Ort
                                                                       FROM xmlTest.dbo.xmlKunden Kunde
                                                                       FOR XML AUTO, TYPE);

— Ergebnis Zeile für Zeile ergänzt mit Satzart in Tabelle speichern
SELECT CAST(‚D‘ As char(1)) As Satzart,
                           CAST(Zeile.Wert.query(‚.‘) As nvarchar(max)) As Zeile
INTO
         #tabXML
FROM       @xmlErgebnis.nodes(‚/Kunde‘) Zeile(Wert);

— Tabelle mit laufender Nummer erweitern
ALTER TABLE #tabXML ADD ZeilenNr int IDENTITY(1,1);

— Start-Tag einfügen
INSERT INTO #tabXml (Satzart, Zeile)
VALUES (‚A‘, ‚<Kunden Datum=“‚ + CONVERT(nvarchar(19), GETDATE(), 127) + ‚“>‘);

— Ende-Tag einfügen
INSERT INTO #tabXml (Satzart, Zeile) VALUES (‚E‘, ‚</Kunden>‘);

— Ausgabe der Daten – sortiert nach Satzart (A, D, E) und Zeilennummer
SELECT Zeile FROM #tabXML ORDER BY Satzart, ZeilenNr;

— Tabelle wieder löschen
DROP
TABLE #tabXML

Dieses Skript speichern wir unter der Bezeichnung xmlSelectKunden.sql.

Dann exportieren wir die Daten der Beispieltabelle per SQLCMD in eine Datei mit der Erweiterung xml.
Wobei wir hier wieder die Parameter -h-1 und -y0 verwenden, um die Ausgabe der Kopfzeile zu vermeiden und die maximale Spaltenbreite auf 1 MB festzulegen. Zusätzlich kommt nun noch der Parameter -u hinzu. Hiermit erhalten wir eine Unicode-Datei.

SQLCMD -S <SqlServer> -i „D:\xmlSelectKunden.sql“ -o „D:\Kunden.xml“ -h-1 -y0 -u

Die so erstellte Textdatei beinhaltet ein valides XML-Dokument und lässt sich durch die Erweiterung xml auch als solches verwenden.

Wert einer Abgeleiteten Spalte mit führenden Nullen ergänzen

Hier am Beispiel eines 7stelligen Werts:
RIGHT(„0000000“ + ((DT_STR,7,1252)@[User::Zahl]),7)

Die Syntax lässt sich auch für eine rechtsbündige Ausgabe verwenden.
Dabei sind die 7 Nullen nur durch 7 Leerzeichen zu ersetzen:
RIGHT(„             “ + ((DT_STR,7,1252)@[User::Zahl]),7)

SQL Server 2008 Installation und „Computer neu starten“

Bei der Installation des SQL Server 2008 wird eine Systemkonfigurationsprüfung durchgeführt.

Eine der Prüfungen ist die Notwendigkeit eines Neustarts des Computers vor der Installation.
Diese Meldung hatte ich bei dem ersten Versuch der Installation. Und auch beim zweiten – wohlgemerkt nach einem Neustart.

Die ersten Recherchen ergaben, dass das wohl am XP SP3 und dem XML Parser 6.0 liegt.
Aber auch nach der Deinstallation des XML Parsers und einem Neustart kam wieder als Prüfungsergebnis „Computer neu starten“.

Die nächsten Recherchen ergaben, dass dieser Eintrag durch einen Eintrag in der Registry namens PendingFileRenameOperations ausgelöst wird. Diesen Eintrag hab ich dann an mehreren Stellen gefunden:

HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Control\Session Manager
HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Control\Session Manager

Nach Löschen der Schlüssel PendingFileRenameOperations in beiden Zweigen wurde die Prüfung erfolgreich abgeschlossen und einer Installation stand jetzt nichts mehr im Wege.

Beide Schlüssel beinhalteten übrigens einen Verweis auf eine DLL, die von meiner Logitech-Webcam verwendet wird.

Dass der SQL Server 2008 sich nicht mit einer Web-Cam verträgt …
Da hätte ich ja auch gleich drauf kommen können …

Security Update Februar 2009

Hier eine kleine Linksammlung zu den bekannten Problemen mit dem Security Bulletin vom Februar 2009:

MS09-004: Vulnerabilities in Microsoft SQL Server could allow remote code execution
http://support.microsoft.com/kb/959420/en-us

MS09-004: Description of the security update for SQL Server 2000 GDR and for MSDE 2000: February 10, 2009
http://support.microsoft.com/kb/960082/en-us

ISA Server 2004 and ISA Server 2006 may be affected by the security updates in Microsoft Knowledge Base articles 960082 and 960083
http://support.microsoft.com/kb/967094/en-us

You cannot apply security update 960082 in a Windows 2000 or Windows Server 2003 environment
http://support.microsoft.com/kb/967092/en-us

SharePoint users are incorrectly offered a SQL Server 2000 Desktop Engine (Windows) update when they try to install the security update in Microsoft Knowledge Base article 960082
http://support.microsoft.com/kb/967096/en-us

SQL Server 2000 Desktop Engine (Windows) (WMSDE) is uninstalled when you use the „Add or Remove Programs“ item in Control Panel to uninstall a security update for SQL Server 2000 and for MSDE 2000
http://support.microsoft.com/kb/967093/en-us

MS09-004: Description of the security update for SQL Server 2000 QFE and for MSDE 2000: February 10, 2009
http://support.microsoft.com/kb/960083/en-us

MS09-004: Description of the security update for SQL Server 2005 QFE: February 10, 2009
http://support.microsoft.com/kb/960090/en-us

MS09-004: Description of the security update for SQL Server 2005 GDR: February 10, 2009 http://support.microsoft.com/kb/960089/en-us

The Windows Internal Database (WYukon) is removed when you use the Add or Remove Programs item in Control Panel to uninstall a security update for SQL Server 2005
http://support.microsoft.com/kb/967095/en-us

Alphanumerische Spalten einer Tabelle

Hier eine kleine Abfrage zur Ermittlung der alphanumerischen Spalten einer Tabelle:

SELECT             o.name As Tabelle, c.name As Spalte, c.column_id As Reihenfolge
FROM
                    sys.columns c INNER JOIN sys.objects o ON c.object_id = o.object_id
                                    INNER JOIN sys.types t ON c.system_type_id = t.system_type_id
WHERE
               o.type = ‚u‘ And t.collation_name Is Not Null
GROUP BY   o.name, c.name, c.column_id
ORDER BY
   o.name, c.column_id

Wie allgemein üblich, übernehme auch ich selbstverständlich keinerlei Haftung für diesen Code.

IsNull in SSIS

Innerhalb der Datentransformation kann es notwendig sein einen NULL-Wert mit einem Standardwert zu ersetzen.
Für diesen Fall gibt es in T-SQL Funktion IsNull. Auch in SSIS gibt es diese Funktion, aber mit einem „kleinen“ Unterschied in der Syntax:

IsNull(Spaltenname)? "Ersatzwert": Spaltenname