Zusammenfassung
Microsoft SQL Server unterstützt Verbindungen zu anderen OLE DB-Datenquellen auf persistenter oder Ad-hoc-Basis. Die persistente Verbindung wird als verknüpfter Server bezeichnet. Eine Ad-hoc-Verbindung, die für eine einzelne Abfrage hergestellt wird, wird als verteilte Abfrage bezeichnet. Microsoft Excel-Arbeitsmappen sind ein Typ von OLE DB-Datenquelle, die Sie auf diese Weise über SQL Server abfragen können. In diesem Artikel wird die Syntax beschrieben, die zum Konfigurieren einer Excel-Datenquelle als verknüpfter Server erforderlich ist, sowie die Syntax, die erforderlich ist, um eine verteilte Abfrage zu verwenden, die eine Excel-Datenquelle abfragt.
Weitere Informationen
Abfragen einer Excel-Datenquelle auf einem verknüpften Server
Sie können SQL Server Management Studio oder Enterprise Manager, eine gespeicherte Systemprozedur, SQL-DMO (Verteilte Verwaltungsobjekte) oder SMO (SQL Server Management Objects) verwenden, um eine Excel-Datenquelle als verknüpften SQL Server zu konfigurieren. (SMO sind nur für Microsoft SQL Server 2005 verfügbar.) In allen diesen Fällen müssen Sie immer die folgenden vier Eigenschaften festlegen:
-
Der Name, den Sie für den verknüpften Server verwenden möchten.
-
Der OLE DB-Anbieter, der für die Verbindung verwendet werden soll.
-
Die Datenquelle oder der vollständige Pfad- und Dateiname für die Excel-Arbeitsmappe.
-
Die Anbieterzeichenfolge, die das Ziel als Excel-Arbeitsmappe identifiziert. Standardmäßig erwartet der Jet-Anbieter eine Access-Datenbank.
Die gespeicherte Systemprozedur sp_addlinkedserver erwartet auch die @srvproduct-Eigenschaft, die ein beliebiger Zeichenfolgenwert sein kann. Hinweis Wenn Sie SQL Server 2005 verwenden, müssen Sie einen Wert angeben, der für die Product name-Eigenschaft in SQL Server Management Studio oder für die @srvproduct-Eigenschaft in der gespeicherten Prozedur für eine Excel-Datenquelle nicht leer ist.
Verwenden von SQL Server Management Studio oder Enterprise Manager zum Konfigurieren einer Excel-Datenquelle als verknüpfter Server
SQL Server Management Studio (SQL Server 2005)
-
Erweitern Sie in SQL Server Management Studio Serverobjekte im Objekt-Explorer.
-
Klicken Sie mit der rechten Maustaste auf Verknüpfte Server, und klicken Sie dann auf Neuer verknüpfter Server.
-
Wählen Sie im linken Bereich die Seite Allgemein aus, und führen Sie dann die folgenden Schritte aus:
-
Geben Sie im ersten Textfeld einen beliebigen Namen für den verknüpften Server ein.
-
Wählen Sie die Option Andere Datenquelle aus.
-
Klicken Sie in der Liste Anbieter auf Microsoft Jet 4.0 OLE DB Provider.
-
Geben Sie im Feld ProduktnameExcel für den Namen der OLE DB-Datenquelle ein.
-
Geben Sie im Feld Datenquelle den vollständigen Pfad und Dateinamen der Excel-Datei ein.
-
Geben Sie im Zeichenfolgenfeld AnbieterExcel 8.0 für eine Excel 2002-, Excel 2000- oder Excel 97-Arbeitsmappe ein.
-
Klicken Sie auf OK, um den neuen verknüpften Server zu erstellen.
-
Hinweis In SQL Server Management Studio können Sie den neuen verknüpften Servernamen nicht erweitern, um die Liste der vom Server enthaltenen Objekte anzuzeigen.
Enterprise Manager (SQL Server 2000)
-
Klicken Sie in Enterprise Manager auf , um den Ordner Sicherheit zu erweitern.
-
Klicken Sie mit der rechten Maustaste auf Verknüpfte Server, und klicken Sie dann auf Neuer verknüpfter Server.
-
Führen Sie auf der Registerkarte Allgemein die folgenden Schritte aus:
-
Geben Sie im ersten Textfeld einen beliebigen Namen für den verknüpften Server ein.
-
Klicken Sie im Feld Servertyp auf Andere Datenquelle.
-
Klicken Sie in der Liste Anbietername auf Microsoft Jet 4.0 OLE DB Provider.
-
Geben Sie im Feld Datenquelle den vollständigen Pfad und Dateinamen der Excel-Datei ein.
-
Geben Sie im Zeichenfolgenfeld AnbieterExcel 8.0 für eine Excel 2002-, Excel 2000- oder Excel 97-Arbeitsmappe ein.
-
Klicken Sie auf OK, um den neuen verknüpften Server zu erstellen.
-
-
Klicken Sie hierauf, um den neuen verknüpften Servernamen zu erweitern, um die Liste der darin enthaltenen Objekte zu erweitern.
-
Klicken Sie unter dem neuen verknüpften Servernamen auf Tabellen. Beachten Sie, dass Ihre Arbeitsblätter und benannten Bereiche im rechten Bereich angezeigt werden.
Verwenden einer gespeicherten Prozedur zum Konfigurieren einer Excel-Datenquelle als verknüpfter Server
Sie können auch die gespeicherte Systemprozedur verwenden, sp_addlinkedserver, um eine Excel-Datenquelle als verknüpften Server zu konfigurieren:
DECLARE @RC intDECLARE @server nvarchar(128)DECLARE @srvproduct nvarchar(128)DECLARE @provider nvarchar(128)DECLARE @datasrc nvarchar(4000)DECLARE @location nvarchar(4000)DECLARE @provstr nvarchar(4000)DECLARE @catalog nvarchar(128)-- Set parameter valuesSET @server = 'XLTEST_SP'SET @srvproduct = 'Excel'SET @provider = 'Microsoft.Jet.OLEDB.4.0'SET @datasrc = 'c:\book1.xls'SET @provstr = 'Excel 8.0'EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider, @datasrc, @location, @provstr, @catalog
Wie bereits erwähnt, erfordert diese gespeicherte Prozedur einen zusätzlichen, beliebigen Zeichenfolgenwert für das argument @srvproduct, der in der Enterprise Manager- und SQL Server Management Studio-Konfiguration als "Produktname" angezeigt wird. Die @location- und @catalog-Argumente werden nicht verwendet.
Verwenden von SQL-DMO zum Konfigurieren einer Excel-Datenquelle als verknüpfter Server
Sie können SQL Distributed Management Objects verwenden, um eine Excel-Datenquelle als verknüpften Server programmgesteuert aus Microsoft Visual Basic oder einer anderen Programmiersprache zu konfigurieren. Sie müssen dieselben vier Argumente angeben, die in der Enterprise Manager- und SQL Server Management Studio-Konfiguration erforderlich sind.
Private Sub Command1_Click() Dim s As SQLDMO.SQLServer Dim ls As SQLDMO.LinkedServer Set s = New SQLDMO.SQLServer s.Connect "(local)", "sa", "password" Set ls = New SQLDMO.LinkedServer With ls .Name = "XLTEST_DMO" .ProviderName = "Microsoft.Jet.OLEDB.4.0" .DataSource = "c:\book1.xls" .ProviderString = "Excel 8.0" End With s.LinkedServers.Add ls s.CloseEnd Sub
Verwenden von SMO zum Konfigurieren einer Excel-Datenquelle als verknüpfter Server
In SQL Server 2005 können Sie SQL Server Management Objects (SMO) verwenden, um eine Excel-Datenquelle programmgesteuert als verknüpften Server zu konfigurieren. Dazu können Sie Microsoft Visual Basic .NET oder eine andere Programmiersprache verwenden. Sie müssen die Argumente angeben, die in der SQL Server Management Studio-Konfiguration erforderlich sind. Das SMO-Objektmodell erweitert und ersetzt das SQL-DMO-Objektmodell (Distributed Management Objects). Da SMO mit SQL Server Version 7.0, SQL Server 2000 und SQL Server 2005 kompatibel ist, können Sie SMO auch für die Konfiguration von SQL Server 2000 verwenden.
Imports Microsoft.SqlServer.Management.SmoImports Microsoft.SqlServer.Management.CommonPublic Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim s As Server Dim conn As ServerConnection Dim ls As LinkedServer conn = New ServerConnection("ServerName\InstanceName", "YourUesrName", "YourPassword") s = New Server(conn) Try ls = New LinkedServer(s, "XLTEST_DMO") With ls .ProviderName = "Microsoft.Jet.OLEDB.4.0" .ProductName = "Excel" .DataSource = "c:\book1.xls" .ProviderString = "Excel 8.0" End With ls.Create() MessageBox.Show("New linked Server has been created.") Catch ex As SmoException MessageBox.Show(ex.Message) Finally ls = Nothing If s.ConnectionContext.IsOpen = True Then s.ConnectionContext.Disconnect() End If End Try End SubEnd ClassAbfragen einer Excel-Datenquelle auf einem verknüpften Server
Nachdem Sie eine Excel-Datenquelle als verknüpften Server konfiguriert haben, können Sie ihre Daten einfach von Query Analyzer oder einer anderen Clientanwendung abfragen. Um beispielsweise die Datenzeilen abzurufen, die in Sheet1 Ihrer Excel-Datei gespeichert sind, verwendet der folgende Code den verknüpften Server, den Sie mithilfe von SQL-DMO konfiguriert haben:
SELECT * FROM XLTEST_DMO...Sheet1$
Sie können OPENQUERY auch verwenden, um den mit Excel verknüpften Excel-Server wie folgt "passthrough" abzufragen:
SELECT * FROM OPENQUERY(XLTEST_DMO, 'SELECT * FROM [Sheet1$]')
Das erste Argument, das OPENQUERY erwartet, ist der verknüpfte Servername. Trennzeichen sind für Arbeitsblattnamen erforderlich, wie oben gezeigt. Sie können auch eine Liste aller Tabellen abrufen, die auf dem verknüpften Excel-Server verfügbar sind, indem Sie die folgende Abfrage verwenden:
EXECUTE SP_TABLES_EX 'XLTEST_DMO'
Abfragen einer Excel-Datenquelle mithilfe verteilter Abfragen
Sie können verteilte SQL Server-Abfragen und die OpenDATASOURCE- oder OPENROWSET-Funktion verwenden, um selten abgerufene Excel-Datenquellen ad hoc abzufragen. Hinweis Wenn Sie SQL Server 2005 verwenden, stellen Sie sicher, dass Sie die Option Ad hoc Distributed Queries aktiviert haben, indem Sie die SQL Server-Oberflächenkonfiguration verwenden, wie im folgenden Beispiel:
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=c:\book1.xls;Extended Properties=Excel 8.0')...Sheet1$Beachten Sie, dass OPENROWSET eine ungewöhnliche Syntax für das zweite Argument ("Provider String") verwendet:
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\book1.xls', Sheet1$)Die Syntax, die ein ActiveX Data Objects (ADO)-Entwickler für das zweite Argument ("Provider String") mit OPENROWSET verwenden kann:
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Data Source=c:\book1.xls;Extended Properties=Excel 8.0', Sheet1$)Diese Syntax löst den folgenden Fehler des Jet-Anbieters aus:
IsAM konnte nicht gefunden werden.
Hinweis Dieser Fehler tritt auch auf, wenn Sie DataSource anstelle von Data Sourceeingeben. Das folgende Argument ist z. B. falsch:
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'DataSource=c:\book1.xls;Extended Properties=Excel 8.0', Sheet1$) Informationsquellen
Da SQL Server-verknüpfte Server und verteilte Abfragen den OLE DB-Anbieter verwenden, gelten hier die allgemeinen Richtlinien und Warnungen zur Verwendung von ADO mit Excel. Weitere Informationen erhalten Sie, um den Artikel in der Microsoft Knowledge Base anzuzeigen:
257819 Verwenden von ADO mit Excel-Daten aus Visual Basic oder VBAWeitere Informationen zu SQL Server-Verwaltungsobjekten finden Sie auf der folgenden MSDN-Website (Microsoft Developer Network):
http://msdn2.microsoft.com/en-us/library/ms162169(ide).aspxWeitere Informationen zum Aktivieren der Ad-hoc-Option Verteilte Abfragen finden Sie auf der folgenden MSDN-Website:
