Sammanfattning
Microsoft SQL Server stöder anslutningar till andra OLE DB-datakällor på en beständig eller ad hoc-basis. Den beständiga anslutningen kallas för en länkad server. en ad hoc-anslutning som görs för en enskild fråga kallas en distribuerad fråga. Microsoft Excel-arbetsböcker är en typ av OLE DB-datakälla som du kan fråga via SQL Server på detta sätt. I den här artikeln beskrivs den syntax som krävs för att konfigurera en Excel-datakälla som en länkad server, samt den syntax som krävs för att använda en distribuerad fråga som frågar en Excel-datakälla.
Mer information
Fråga en Excel-datakälla på en länkad server
Du kan använda SQL Server Management Studio eller Enterprise Manager, en systemlagrad procedur, SQL-DMO (Distributed Management Objects) eller SMO (SQL Server Management Objects) för att konfigurera en Excel-datakälla som en länkad SQL Server-Server. (SMO är endast tillgängliga för Microsoft SQL Server 2005.) I alla dessa fall måste du alltid ange följande fyra egenskaper:
-
Det namn som du vill använda för den länkade servern.
-
Den OLE DB- Provider som ska användas för anslutningen.
-
Datakällan eller fullständig sökväg och filnamn för Excel-arbetsboken.
-
Providersträngen, som identifierar målet som en Excel-arbetsbok. Som standard förväntar sig Jet-providern en Access-databas.
Den systemlagrade proceduren sp_addlinkedserver förväntar sig också egenskapen @srvproduct , vilket kan vara vilket strängvärde som helst. Notera Om du använder SQL Server 2005 måste du ange ett värde som inte är tomt för egenskapen Produktnamn i SQL Server Management Studio eller för egenskapen @srvproduct i den lagrade proceduren för en Excel-datakälla.
Använda SQL Server Management Studio eller Enterprise Manager för att konfigurera en Excel-datakälla som en länkad server
SQL Server Management Studio (SQL Server 2005)
-
Expandera serverobjekt i Object Exploreri SQL Server Management Studio.
-
Högerklicka på länkade servraroch klicka sedan på ny länkad server.
-
Välj sidan Allmänt i den vänstra rutan och följ sedan dessa steg:
-
Skriv ett namn på den länkade servern i den första textrutan.
-
Välj alternativet annan datakälla .
-
Klicka på Microsoft Jet 4,0 OLE DB Provideri listan Provider .
-
Skriv Excel som namn på OLE DB-datakällan i rutan Produktnamn .
-
Skriv den fullständiga sökvägen och filnamnet för Excel-filen i rutan data källa .
-
I rutan providersträng skriver du Excel 8,0 för en Excel 2002-, Excel 2000-eller Excel 97-arbetsbok.
-
Klicka på OK för att skapa den nya länkade servern.
-
Notera I SQL Server Management Studio kan du inte expandera det nya länkade Server namnet för att visa listan över objekt som servern innehåller.
Enterprise Manager (SQL Server 2000)
-
I Enterprise Manager, klicka för att expandera mappen Security .
-
Högerklicka på länkade servraroch klicka sedan på ny länkad server.
-
Gör så här på fliken Allmänt :
-
Skriv ett namn på den länkade servern i den första textrutan.
-
Klicka på annan datakällai rutan Server typ .
-
Klicka på Microsoft Jet 4,0 OLE DB Provideri listan Providernamn .
-
Skriv den fullständiga sökvägen och filnamnet för Excel-filen i rutan data källa .
-
I rutan providersträng skriver du Excel 8,0 för en Excel 2002-, Excel 2000-eller Excel 97-arbetsbok.
-
Klicka på OK för att skapa den nya länkade servern.
-
-
Klicka för att expandera det nya länkade servernamnet för att expandera listan med objekt som den innehåller.
-
Klicka på tabellerunder det nya länkade servernamnet. Lägg märke till att dina kalkylblad och namngivna områden visas i den högra rutan.
Använda en lagrad procedur för att konfigurera en Excel-datakälla som en länkad server
Du kan också använda den systemlagrade proceduren sp_addlinkedserver för att konfigurera en Excel-datakälla som en länkad server:
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
Som nämnts ovan kräver den här lagrade proceduren ett ytterligare, godtyckligt strängvärde för argumentet @srvproduct , som visas som "produktnamn" i Enterprise Manager och SQL Server Management Studio-konfigurationen. Argumenten @location och @catalog används inte.
Använda SQL-DMO för att konfigurera en Excel-datakälla som en länkad server
Du kan använda SQL-distribuerade hanteringsobjekt för att konfigurera en Excel-datakälla som en länkad server programmässigt från Microsoft Visual Basic eller ett annat programmeringsspråk. Du måste ange samma fyra argument som krävs i Enterprise Manager och SQL Server Management Studio-konfigurationen.
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
Använda SMO för att konfigurera en Excel-datakälla som en länkad server
I SQL Server 2005 kan du använda SQL Server Management Objects (SMO) för att konfigurera en Excel-datakälla som en länkad server programmässigt. Om du vill göra detta kan du använda Microsoft Visual Basic .NET eller något annat programmeringsspråk. Du måste ange argumenten som krävs i SQL Server Management Studio-konfigurationen. SMO-objektmodellen utökar och ersätter objektmodellen för distribuerad hanteringsobjekt (SQL-DMO). Eftersom SMO är kompatibelt med SQL Server version 7,0, SQL Server 2000 och SQL Server 2005, kan du också använda SMO för konfiguration av SQL Server 2000.
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 ClassFråga en Excel-datakälla på en länkad server
När du har konfigurerat en Excel-datakälla som en länkad server kan du enkelt fråga data från Query Analyzer eller ett annat klientprogram. Om du till exempel vill hämta dataraderna som lagras i Blad1 i Excel-filen, använder följande kod den länkade server som du konfigurerade med hjälp av SQL-DMO:
SELECT * FROM XLTEST_DMO...Sheet1$
Du kan också använda OPENQUERY för att fråga den länkade Excel-servern på ett "passthrough" sätt, enligt följande:
SELECT * FROM OPENQUERY(XLTEST_DMO, 'SELECT * FROM [Sheet1$]')
Det första argumentet som OPENQUERY förväntar sig är det länkade servernamnet. Avgränsare krävs för kalkylbladsnamn, som visas ovan. Du kan också hämta en lista över alla tabeller som är tillgängliga på den länkade Excel-servern med hjälp av följande fråga:
EXECUTE SP_TABLES_EX 'XLTEST_DMO'
Fråga en Excel-datakälla med hjälp av distribuerade frågor
Du kan använda SQL Server distribuerade frågor och funktionen OPENDATASOURCE eller OPENROWSET att fråga sällan används Excel-datakällor på ad hoc-basis. Notera Om du använder SQL Server 2005, kontrollera att du har aktiverat alternativet ad hoc-distribuerade frågor med hjälp av SQL Server yta Area Configuration, som i följande exempel:
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=c:\book1.xls;Extended Properties=Excel 8.0')...Sheet1$Observera att OPENROWSET använder en ovanlig syntax för det andra argumentet ("Provider String"):
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\book1.xls', Sheet1$)Syntaxen som ett ActiveX Data Objects (ADO) utvecklare kan förvänta sig att använda för det andra ("Provider String") argumentet med OPENROWSET:
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Data Source=c:\book1.xls;Extended Properties=Excel 8.0', Sheet1$)Den här syntaxen väcker följande fel från Jet-providern:
Det gick inte att hitta installerbar ISAM.
Notera Det här felet uppstår även om du anger DataSource i stället för data källa. Följande argument är till exempel felaktigt:
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'DataSource=c:\book1.xls;Extended Properties=Excel 8.0', Sheet1$) Referenser
Eftersom SQL Server länkade servrar och distribuerade frågor använder OLE DB-Provider, gäller de allmänna riktlinjerna och försiktighetsåtgärder om hur du använder ADO med Excel här. Om du vill veta mer klickar du på följande artikelnummer och visar artikeln i Microsoft Knowledge Base:
257819 hur du använder ADO med Excel-data från Visual Basic eller VBAMer information om SQL Server Management-objekt finns på följande Microsoft Developer Network (MSDN)-webbplats:
http://msdn2.microsoft.com/en-us/library/ms162169(ide).aspxMer information om hur du aktiverar alternativet ad hoc-distribuerade frågor finns på följande MSDN-webbplats:
