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