Tweet
Hallo zusammen,
ich muss mich nochmals an euch wenden, weil ich folgende herausforderungen bis dato nicht meistern konnte:
den nachfolgenden code habe ich im internet gefunden. er dient dazu eine datenbank (ms sql 2005) in eine excel-datei zu exportieren. eine datenbank wird in ein excelfile abgelegt, die enthaltenen tabellen finden sich anschliessend in den tabellenblätter wieder.
ich kenne mich zu wenig mit sql server 2005 aus, um den code vollständig zu verstehen. er funktioniert bei mir eigentlich sehr gut, allerdings möchte ich noch vier verbesserungen/veränderungen vornehmen - und weiss nicht genau wie:
1. Sobald der Ausgabepfad ein Leerzeichen beinhaltet, wird am Ende der Prozedur die erstellte Access-Datenbank nicht wieder gelöscht. Da ich (mit Hilfe eines Java-Programms) dynamisch sehr umfangreiche SQL-Files erstellen lasse, welche anschliessend Datenbanken erzeugen und diese dann in Excel-Dateien geschrieben werden, kann es trotzdem recht störend sein, wenn jedes Mal 30 Access-Datenbanken zusätzlich zu den 30 Excels erstellt werden. Diese müssen dann von Hand gelöscht werden.
Wie gesagt: Wenn keine Leerzeichen im Ausgabepfad sind, funktioniert das Löschen der Access-Dateien reibungslos.
2. Würde ich gerne alle Spalten vom Datentyp float auf zwei Nachkommastellen runden. Wie lässt sich dies am besten lösen?
Oder muss ich tatsächlich vor dem Exportieren die Datentypen der Datenbanken auf zwei Nachkommastellen setzen ? (Wichtigste Frage)
3. Gibt es eine Möglichkeit das zu erstellende Excel vom SQL-Code aus in irgendeiner Weise zu formatieren? Z.b. die erste Zeile in fetter Schrift oder aber auch die ersten beiden Spalten sollten etwas breiter sein als per default... etc.?
4. Eine Knacknuss: Es kann vorkommen, dass eine Tabelle mehr als 256 Spalten beinhaltet - und somit über den Grenzwert von MS Excel Dateien (256 Spalten) steigt. Damit wird keine Exceldatei erstellt, sondern eine Fehlermeldung erzeugt. Gibt es eine Möglichkeit, dass der Server automatisch - sobald mehr als 256 Spalten - den Export in zwei Exceltabellenblätter vornimmt. Allerdings müsste hierbei auf der Exceltabelle die eindeutige Id jeder Entität nochmals ausgegeben werden. Bin mir nicht sicher, ob es hierfür eine Lösung gibt.
Weiss jemand, wie ich obengenannte Herausforderungen in den Griff bekommen könnte?
Ich freue mich über jeden Vorschlag.
Vielen Dank
Barbara
ich muss mich nochmals an euch wenden, weil ich folgende herausforderungen bis dato nicht meistern konnte:
den nachfolgenden code habe ich im internet gefunden. er dient dazu eine datenbank (ms sql 2005) in eine excel-datei zu exportieren. eine datenbank wird in ein excelfile abgelegt, die enthaltenen tabellen finden sich anschliessend in den tabellenblätter wieder.
ich kenne mich zu wenig mit sql server 2005 aus, um den code vollständig zu verstehen. er funktioniert bei mir eigentlich sehr gut, allerdings möchte ich noch vier verbesserungen/veränderungen vornehmen - und weiss nicht genau wie:
1. Sobald der Ausgabepfad ein Leerzeichen beinhaltet, wird am Ende der Prozedur die erstellte Access-Datenbank nicht wieder gelöscht. Da ich (mit Hilfe eines Java-Programms) dynamisch sehr umfangreiche SQL-Files erstellen lasse, welche anschliessend Datenbanken erzeugen und diese dann in Excel-Dateien geschrieben werden, kann es trotzdem recht störend sein, wenn jedes Mal 30 Access-Datenbanken zusätzlich zu den 30 Excels erstellt werden. Diese müssen dann von Hand gelöscht werden.
Wie gesagt: Wenn keine Leerzeichen im Ausgabepfad sind, funktioniert das Löschen der Access-Dateien reibungslos.
2. Würde ich gerne alle Spalten vom Datentyp float auf zwei Nachkommastellen runden. Wie lässt sich dies am besten lösen?
Oder muss ich tatsächlich vor dem Exportieren die Datentypen der Datenbanken auf zwei Nachkommastellen setzen ? (Wichtigste Frage)
3. Gibt es eine Möglichkeit das zu erstellende Excel vom SQL-Code aus in irgendeiner Weise zu formatieren? Z.b. die erste Zeile in fetter Schrift oder aber auch die ersten beiden Spalten sollten etwas breiter sein als per default... etc.?
4. Eine Knacknuss: Es kann vorkommen, dass eine Tabelle mehr als 256 Spalten beinhaltet - und somit über den Grenzwert von MS Excel Dateien (256 Spalten) steigt. Damit wird keine Exceldatei erstellt, sondern eine Fehlermeldung erzeugt. Gibt es eine Möglichkeit, dass der Server automatisch - sobald mehr als 256 Spalten - den Export in zwei Exceltabellenblätter vornimmt. Allerdings müsste hierbei auf der Exceltabelle die eindeutige Id jeder Entität nochmals ausgegeben werden. Bin mir nicht sicher, ob es hierfür eine Lösung gibt.
Weiss jemand, wie ich obengenannte Herausforderungen in den Griff bekommen könnte?
Ich freue mich über jeden Vorschlag.
Vielen Dank
Barbara
Code:
DECLARE @pfad varchar(255)
SET @pfad = D:\IrgendeinOutputpfad\'
DECLARE @hr int -- Returncode der sp_OA... Aufrufe
DECLARE @katalog int -- Objektvariable für ADOX.Catalog
DECLARE @verbindung int -- Objektvariable für ADO.Connection
DECLARE @dbname varchar(255) -- Name der temporären Access-Datenbank
DECLARE @conString varchar(512) -- Verbindungszeichenfolge für Access-Datenbank
DECLARE @quelle varchar(255) -- Hilfsfeld für Fehlerbehandlung
DECLARE @beschreibung varchar(255) -- Hilfsfeld für Fehlerbehandlung
DECLARE @dummy int -- Dummy Output Parameter
DECLARE @Excel_Mappe varchar(255) -- Vollständiger Name der Ziel Excelmappe
DECLARE @tabelle varchar(255) -- Name der zu exportierenden Tabelle
DECLARE @besitzer varchar(255) -- Besitzer der zu exportierenden Tabelle
DECLARE @exec varchar(4000) -- Hilfsvariable für dynamische Ausführungen
-- Cursor zum Ermitteln aller Tabellen ohne Text- und Image-Spalten
DECLARE curTabellen CURSOR FAST_FORWARD FOR
SELECT t.TABLE_SCHEMA, t.TABLE_NAME FROM INFORMATION_SCHEMA.TABLES t
WHERE TABLE_TYPE = 'BASE TABLE'
AND NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('text', 'ntext', 'image')
AND TABLE_CATALOG = t.TABLE_CATALOG AND
TABLE_SCHEMA = t.TABLE_SCHEMA AND
TABLE_NAME = t.TABLE_NAME)
ORDER BY TABLE_NAME
-- Erzeugen eines ADOX-Katalog Objekts
EXEC @hr = master.dbo.sp_OACreate 'ADOX.Catalog', @katalog OUTPUT
IF @hr <> 0 -- Fehlerbehandlung
BEGIN
EXEC sp_OAGetErrorInfo @katalog, @quelle OUTPUT, @beschreibung OUTPUT
RAISERROR ( 'Fehler beim Erstellen des ADOX.Catalog Objekts: %s', 10 , -1, @beschreibung)
END
-- Zufälliges Erzeugen eines Datenbank-Namens
SET @dbname = @pfad + CAST(newid() AS varchar(100)) + '.MDB'
-- Erstellen der Verbindungszeichenfolge
SET @conString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' + @dbname
-- Erstellen einer neuen (leeren) Access-Datenbank
-- Dieser Schritt ist notwendig, da nur über eine Verbindung zu einer Access-Datenbank
-- die benötigte Jet-Funktionalität zur Verfügung steht
EXEC @hr = master.dbo.sp_OAMethod @katalog, 'Create',
@dummy output,
@conString
IF @hr <> 0 -- Fehlerbehandlung
BEGIN
EXEC sp_OAGetErrorInfo @katalog, @quelle OUTPUT, @beschreibung OUTPUT
RAISERROR ( 'Fehler beim Erstellen der Access-Datenbank %s: %s', 10 , -1, @dbname, @beschreibung)
END
-- Variable @verbindung auf die Eigenschaft "ActiveConnection" des Katalog-Objekts setzen
EXEC @hr = master.dbo.sp_OAGetProperty @katalog, 'ActiveConnection', @verbindung OUTPUT
IF @hr <> 0 -- Fehlerbehandlung
BEGIN
EXEC sp_OAGetErrorInfo @verbindung, @quelle OUTPUT, @beschreibung OUTPUT
RAISERROR ( 'Fehler beim Zugriff auf Access-Datenbank %s: %s', 10 , -1, @dbname, @beschreibung)
END
-- Öffnen des Cursors und Export für alle gefundenen Tabellen durchlaufen
OPEN curTabellen
FETCH NEXT FROM curTabellen INTO @besitzer, @tabelle
WHILE @@FETCH_STATUS = 0
BEGIN
-- Dynamisches Erzeugen der SELECT INTO Anweisung
SET @exec = 'SELECT TOP 65535 * INTO [Excel 8.0;Database=' + @pfad + db_name() + '.xls].[' + @besitzer + '_' + @tabelle + '] FROM [ODBC;Driver=SQL Server;Database=' + DB_NAME() + ';Server=' + @@SERVERNAME + ';Trusted_Connection=Yes;].[' + @besitzer + '.' + @tabelle + ']'
EXEC @hr = master.dbo.sp_OAMethod @verbindung, 'Execute', @dummy output, @exec
IF @hr <> 0 -- Fehlerbehandlung
BEGIN
EXEC sp_OAGetErrorInfo @verbindung, @quelle OUTPUT, @beschreibung OUTPUT
RAISERROR ( 'Fehler beim Export: (%s): %s', 10 , -1, @exec, @beschreibung)
END
FETCH NEXT FROM curTabellen INTO @besitzer, @tabelle
END
-- "Aufräumarbeiten"
CLOSE curTabellen
DEALLOCATE curTabellen
EXEC @hr = master.dbo.sp_OAMethod @verbindung, 'Close'
IF @hr <> 0 -- Fehlerbehandlung
BEGIN
EXEC sp_OAGetErrorInfo @verbindung, @quelle OUTPUT, @beschreibung OUTPUT
RAISERROR ( 'Fehler beim Schliessen der Verbindung zur Access-Datenbank: %s', 10 , -1, @beschreibung)
END
EXEC @hr = master.dbo.sp_OADestroy @verbindung
IF @hr <> 0 -- Fehlerbehandlung
BEGIN
EXEC sp_OAGetErrorInfo @verbindung, @quelle OUTPUT, @beschreibung OUTPUT
RAISERROR ( 'Fehler beim Zerstören des ADO-Connection Objekts: %s', 10 , -1, @beschreibung)
END
EXEC @hr = master.dbo.sp_OADestroy @katalog
IF @hr <> 0 -- Fehlerbehandlung
BEGIN
EXEC sp_OAGetErrorInfo @verbindung, @quelle OUTPUT, @beschreibung OUTPUT
RAISERROR ( 'Fehler beim Zerstören des ADO-Connection Objekts: %s', 10 , -1, @beschreibung)
END
-- Temporäre Access-Datenbank löschen
SET @exec = 'DEL ' + @dbname
EXEC master.dbo.xp_cmdshell @exec ,NO_OUTPUT
GO
Comment