Abfrage - Ausgabe in Datei

[odw-mher]

Hallo,
ich muss über ein Java-Programm per JDBC eine StoredProcedure auf dem SQLServer ausführen. In dieser StoredProcedure soll nur eine kleine Datenbankabfrage gemacht werden und das Ergebnis aber in eine CSV-oder Textdatei auf das lokale Laufwerk des SQLServers gespeichert werden.

Mein Problem ist, dass ich bis jetzt keine Möglichkeit gefunden habe, in Transakt-SQL Abfrageergebnisse in eine Datei zu speichern.
Unter Informix z. B. gibt es einen Befehl "unload to" und die Sache ist damit erledigt. Aber beim SQLServer finde ich nichts gleichwertiges.

Kann mir jemand sagen, wie ich meine Abfrageergebnisse einfach in eine Datei speichern kann.

Vielen Dank im voraus

Gruß
Markus

[Andreas Kosch]

Hallo,

wenn man sich die Auswertungen der Sicherheitslücken der verschiedenen SQL-Datenbanken anschaut, kommt der Microsoft SQL Server gut weg. Insbesondere der MS SQL Server 2005 erhält dort ein ausgezeichnetes Ergebnis. Zu einem geringen Teil wird das auch mit den Restriktionen zusammenhängen, die der MS SQL Server für externe Ressourcen erzwingt. Analog zu den Regeln einer Firewall geht es dabei vor allem um die Frage, von welcher Seite aus die Kommunikation initialisiert wird. Im konkreten Fall bedeutet dies, dass der Export der Ergebnismenge einer SELECT-Abfrage vom aufrufenden Programm angestoßen wird, wie das folgende Beispiel zeigt (die Datenbank AdventureWorks liegt auf dem Server P4D3GHZ, auf den der Prozess über die integrierte Windows-Authentifizierung zugreift). Der Schalter -o legt die Ausgabedatei fest:

C:\Temp>osql -SP4D3GHZ -E -dAdventureWorks -Q"SELECT * FROM Person.Address" -o C:\Temp\Ergebnis.txt

C:\Temp>

Der Aufrufer (entweder der Anwender, der das Kommandozeilen-Tool osql.exe im Fenster der Eingabeaufforderung startet oder die aufrufende Anwendung, die osql.exe als Prozess startet), ist dafür verantwortlich, dass der Aufrufer für das festgelegte Ausgabeverzeichnis auch die erforderlichen Schreibberechtigungen hat.

Wenn die Datei innerhalb einer Stored Procedure der Datenbank angelegt werden soll, stellt die Systemprozedur xp_cmdshell den Aufruf "von außen her" sicher:

EXEC master..xp_cmdshell "osql -SP4D3GHZ -E -dAdventureWorks -Q""SELECT * FROM Person.Address"" -o C:\Temp\Ergebnis.txt";
GO

Altenativ steht das Dienstprogramm bcp.exe zur Verfügung:

EXEC master..xp_cmdshell "bcp ""SELECT * FROM AdventureWorks.Person.Address"" queryout C:\Temp\Ergebnis2.txt -c -T";
GO

Wie die Hilfeseiten zu den beiden Alternativen zeigen, können weitere Details wie die Struktur der Datei bezüglich Spalten- und Zeilentrenner, der zu verwendende Zeichensatz etc. über weitere Argumente gesteuert werden.

P.S: Die Gegenrichtung - also der Import von Daten aus einer externen Datei - ist beim MS SQL Server viel einfacher, denn dort geht es ja nur um einen Lesezugriff auf die Datei.

[odw-mher]

Hallo,
erstmal vielen herzlichen Dank für die schnelle und gut erklärte Antwort.
Es funktioniert einwandfrei!

Nur ein kleines Schönheitsproblem habe ich noch.
Wenn die Ausgabedatei erzeugt wird, stehen unter den gewünschten Ergebnissen immer die SQL-Server Meldungen. (z. B. "1 rows affected").
Kann man das irgendwie abschalten?

[Andreas Kosch]

Hallo,

Kann man das irgendwie abschalten?

Ja, es muss nur eine SQL-Batchanweisung ausgeführt werden, bei der im 1. Teil die Anzahl der betroffenen Datensätze über SET NOCOUNT ON ausgeblendet wird:

SET NOCOUNT ON;SELECT * FROM Person.Address

Die vollständige Anweisung lautet dann:

EXEC master..xp_cmdshell "osql -SP4D3GHZ -E -dAdventureWorks -Q""SET NOCOUNT ON;SELECT * FROM Person.Address"" -o C:\Temp\Ergebnis.txt";
GO

[MikeIO]

Hallo,

soweit so gut. Hab eine Datei, allerdings mit isql und sql server 2000, erzeugt. leider ist vor jeder zeile ein Leerzeichen und nach jeder zeile kommen ein paar hundert leerzeichen. Wie bekomme ich die leerzeichen weg?

Viele Grüße
MikeIO

[Andreas Kosch]

Hallo,

Wie bekomme ich die leerzeichen weg?

ich kann das Problem der nachfolgenden Leerzeilen nicht reproduzieren. Tritt dieser Effekt auch bei den von mir verwendeten Beispielen für die AdventureWorks-Datenbank auf? Unterscheidet sich das Ergebnis von osql und bcp in Bezug auf das führende Leerzeichen?

[MikeIO]

Hi,

danke für die schnelle Antwort.
Hab das reine SQL mal im Query Analyzer ausprobiert, das funktioniert einwandfrei. Bei osql hab ich vorgestellt auch ein Leerzeichen und nachfolgend deutlich mehr leerzeichen. Bcp werd ich mir mal ansehen.

Die AdventureWorks DB ist hier leider nicht installiert. Hab aber mal ein select auf eine andere Tabelle gemacht, da hatte ich das gleiche Problem. Im Select hab ich auch schonmal mit ltrim(rtrim()) gearbeitet, leider erfolglos.

Viele Grüße
MikeIO

[MikeIO]

Also mit bcp funktioniert es, aber wie kann ich dort statt einer geschriebenen Abfrage eine SQL-Datei anhängen? Die abfrage ist etwas komplexer.
Außerdem muss ich dem bcp aufruf noch die Datenbank auf der die query ausgeführt werden soll mitgeben.