Mit Hilfe von ADOX (ActiveX Data Objects Extensions for Data Definition Language and Security) können Daten zwischen verschiedenen Datenquellen importiert und exportiert werden. Wird die SELECT INTO-Anweisung verwendet, können Zieltabellen auch dynamisch erzeugt werden.

Um den vollständigen Umfang des JET-OLEDB-Providers nutzen zu können, muss jedoch zunächst immer eine Verbindung zu einer Access-Datenbank hergestellt werden, ansonsten steht nicht die volle Funktionalität zur Verfügung.

Im folgenden Beispiel sollen alle Tabellen einer Datenbank in eine neu zu erstellende Excel-Arbeitsmappe exportiert werden. Für jede Tabelle wird ein neues Arbeitsblatt erstellt.

Einschränkungen

Excel unterstützt nicht alle Datentypen und auch hinsichtlich der Datenmenge gibt es natürlich Einschränkungen. Das folgende Beispiel exportiert nur SQL Server Tabellen, die keine image-, text- oder ntext-Spalten enthalten. Außerdem werden maximal 65.535 Zeilen exportiert.

Ablauf des Beispielscripts

Das Beispielscript exportiert alle Tabellen der aktuellen Datenbank in eine Excel-Arbeitsmappe mit dem gleichen Namen wie die Datenbank. Falls ein Tabellenblatt mit dem gleichen Name wie die Tabelle in der Mappe bereits existiert wird nicht exportiert.

Die einzelnen Schritte

Deklaration der Variablen
Festlegen des Pfads für Arbeitsmappe und Access-Datenbank
Zufälliges Generieren eines Namens für die Access-Datenbank
Erstellen einer neuen, leeren Access-Datenbank
Durchlaufen aller SQL Server Tabellen ohne image-/text-Spalten
- Ausführen einer SELECT INTO-Anweisung, die Daten aus der SQL Server Tabelle in das Excel-Arbeitsblatt kopiert
Schließen der Verbindung zur Access-Datenbank und Löschen der leeren Access-Datenbank

Deklaration von Variablen und des Cursors

Im ersten Abschnitt der Prozedur werden die Variablen deklariert.

DECLARE @pfad varchar (255)
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

Außerdem wird ein Cursor deklariert, der den Tabellennamen und Besitzer aller Tabellen ohne Text- und Image-Spalten in der aktuellen Datenbank ermittelt:

-- 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

Festlegen des Pfads

Die Variable @pfad wird mit dem Zielpfad für die Excel-Mappe und die Access-Datenbank vorbelegt. Dieser Pfad ist relativ zum SQL Server; d.h. der SQL Server Dienst muss Schreib-Zugriff auf dieses Verzeichnis besitzen.

SET @pfad = 'D:\'

Zufälliges Generieren eines Namens für die Access-Datenbank

Mit Hilfe der Funktion NewID() wird ein zufälliger Name für die benötigte Access-Datenbank generiert. Die Datenbank wird am Schluss des Scripts wieder gelöscht

-- Zufälliges Erzeugen eines Datenbank-Namens
SET @dbname = @pfad + CAST(newid () AS varchar(100 )) + '.MDB'

Erstellen einer neuen, leeren Access Datenbank

Mit Hilfe der Methode .Create des ADOX-Catalog Objekts kann eine neue Access-Datenbank angelegt werden. War das Anlegen erfolgreich, so kann anschließend über die Eigenschaft .ActiveConnection des Catalog-Objekts eine ADO-Verbindung angesprochen werden.

-- Erzeugen eines ADOX-Katalog Objekts
EXEC @hr = master.dbo .sp_OACreate 'ADOX.Catalog' , @katalog OUTPUT
IF @hr <> 0   -- Fehlerbehandlung ...

-- 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...

-- Variable @verbindung auf die Eigenschaft "ActiveConnection" des Katalog-Objekts setzen
EXEC @hr = master. dbo.sp_OAGetProperty @katalog, 'ActiveConnection', @verbindung OUTPUT
IF @hr <> 0  -- Fehlerbehandlung...

Durchlaufen des Cursors/der Tabellen

-- Ö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 Erstellen der SELECT INTO-Anweisung

Innerhalb der Schleife wird für jede Tabelle die SELECT-INTO Anweisung dynamisch erzeugt.
Dabei wird der Name der aktuellen Datenbank als Name für die Excel-Arbeitsmappe verwendet.
Für jede Tabelle wird ein neues Arbeitsblatt mit dem Namen besitzer_tabelle erzeugt.

Die Verbindung zum SQL Server wird mit Hilfe des ODBC Treibers für den SQL Server mit vertrauter Verbindung hergestellt.

-- 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 + ']'

Anschließend kann die SELECT-INTO Anweisung ausgeführt werden...

EXEC @hr = master.dbo .sp_OAMethod @verbindung , 'Execute', @dummy output, @exec
IF @hr <> 0 -- Fehlerbehandlung...

... und dann die nächste Tabelle gelesen werden.

FETCH NEXT FROM curTabellen INTO @besitzer, @tabelle
END

Aufräumarbeiten

Nachdem alle Tabellen exportiert wurden, werden der Cursor und die Verbindung zur Access-Datenbank geschlossen. Anschließend wird die erzeugte Access-Datenbank gelöscht.

-- "Aufräumarbeiten"
CLOSE curTabellen
DEALLOCATE curTabellen

EXEC @hr = master. dbo .sp_OAMethod @verbindung, 'Close'
EXEC @hr = master.dbo .sp_OADestroy @verbindung
EXEC @hr = master.dbo .sp_OADestroy @katalog
-- Temporäre Access-Datenbank löschen
SET @exec = 'DEL ' + @dbname
EXEC master.dbo.xp_cmdshell @exec ,NO_OUTPUT

Hier können Sie sich das komplette Script incl. Fehlerbehandlung ansehen.

Link-Tipp zum Thema "Microsoft SQL Server": sql.reproms.si/live/default.asp

Die Homepage der slovenischen SQL Server User Group mit Code-Library, White Papers und einigem mehr.