Shrnutí
Microsoft SQL Server podporuje připojení k jiným zdrojům dat OLE DB na trvalém nebo ad hoc základě. Trvalé připojení je označováno jako propojený server. připojení ad hoc, které je provedeno v zájmu jednoho dotazu, se nazývá distribuovaný dotaz. Sešity aplikace Microsoft Excel jsou jedním typem zdroje dat OLE DB, který lze tímto způsobem zadávat prostřednictvím serveru SQL Server. Tento článek popisuje syntaxi, která je nezbytná ke konfiguraci zdroje dat aplikace Excel jako propojeného serveru, a také syntaxi nutnou k použití distribuovaného dotazu, který se dotazuje na zdroj dat aplikace Excel.
Další informace
Dotaz na zdroj dat aplikace Excel na propojeném serveru
Ke konfiguraci zdroje dat aplikace Excel jako propojeného serveru SQL Server můžete použít SQL Server Management Studio nebo Enterprise Manager, systémovou uloženou proceduru, SQL-DMO (distribuované objekty správy) nebo SMO (SQL Server Management Objects). (Společnost SMO je k dispozici pouze pro Microsoft SQL Server 2005.) Ve všech těchto případech je třeba vždy nastavit následující čtyři vlastnosti:
-
Název , který chcete použít pro propojený server.
-
Zprostředkovatel OLE DB, který má být použit pro připojení.
-
Zdroj dat nebo úplná cesta a název souboru sešitu aplikace Excel.
-
Řetězec zprostředkovatele, který identifikuje cíl jako sešit aplikace Excel. Ve výchozím nastavení očekává zprostředkovatel Jet databázi aplikace Access.
Systémová uložená procedura sp_addlinkedserver také očekává vlastnost @srvproduct , což může být jakákoli řetězcová hodnota. Poznámka: Používáte-li SQL Server 2005, je třeba zadat hodnotu, která není pro vlastnost názvu produktu v aplikaci SQL Server Management Studio nebo pro vlastnost @srvproduct v uložené proceduře zdroje dat aplikace Excel prázdná.
Použití serveru SQL Server Management Studio nebo Enterprise Manager ke konfiguraci zdroje dat aplikace Excel jako propojeného serveru
SQL Server Management Studio (SQL Server 2005)
-
V aplikaci SQL Server Management Studio rozbalte objekty serveru v Průzkumníku objektů.
-
Pravým tlačítkem myši klepněte na položku propojené serverya potom klepněte na příkaz nový propojený server.
-
V levém podokně vyberte stránku Obecné a proveďte následující kroky:
-
Do prvního textového pole zadejte libovolný název propojeného serveru.
-
Vyberte možnost jiného zdroje dat .
-
V seznamu zprostředkovatelů klikněte na položku Microsoft Jet 4,0 OLE DB Provider.
-
Do pole název produktu zadejte text Excel pro název zdroje dat OLE DB.
-
Do pole zdroj dat zadejte úplnou cestu a název souboru aplikace Excel.
-
Do pole řetězce zprostředkovatele zadejte Excel 8,0 pro sešit excel 2002, excel 2000 nebo Excel 97.
-
Klepnutím na tlačítko OK vytvoříte nový propojený server.
-
Poznámka: V aplikaci SQL Server Management Studio nelze rozbalit nový název propojeného serveru a zobrazit tak seznam objektů, které server obsahuje.
Správce rozlehlé sítě (SQL Server 2000)
-
V nástroji Enterprise Manager rozbalte klepnutím složku Security .
-
Pravým tlačítkem myši klepněte na položku propojené serverya potom klepněte na příkaz nový propojený server.
-
Na kartě Obecné postupujte následujícím způsobem:
-
Do prvního textového pole zadejte libovolný název propojeného serveru.
-
V poli Typ serveru klepněte na možnost jiný zdroj dat.
-
V seznamu názvů zprostředkovatelů klepněte na položku Microsoft Jet 4,0 OLE DB Provider.
-
Do pole zdroj dat zadejte úplnou cestu a název souboru aplikace Excel.
-
Do pole řetězce zprostředkovatele zadejte Excel 8,0 pro sešit excel 2002, excel 2000 nebo Excel 97.
-
Klepnutím na tlačítko OK vytvoříte nový propojený server.
-
-
Klepnutím rozbalíte nový název propojeného serveru a rozbalíte tak seznam objektů, které obsahuje.
-
Pod novým názvem propojeného serveru klepněte na tlačítko tabulky. Všimněte si, že se listy a pojmenované oblasti zobrazují v pravém podokně.
Použití uložené procedury ke konfiguraci zdroje dat aplikace Excel jako propojeného serveru
Můžete také použít systémovou uloženou proceduru sp_addlinkedserver ke konfiguraci zdroje dat aplikace Excel jako propojeného serveru:
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
Jak bylo uvedeno výše, tato uložená procedura vyžaduje další, libovolnou řetězcovou hodnotu pro @srvproduct argument, která se v konfiguraci Enterprise Manager a serveru SQL Server Management Studio zobrazí jako "název produktu". Argumenty @location a @catalog nejsou použity.
Konfigurace zdroje dat aplikace Excel jako propojeného serveru pomocí příkazu SQL-DMO
Objekty služby SQL Distributed Management Object lze použít ke konfiguraci zdroje dat aplikace Excel jako propojeného serveru programově z jazyka Microsoft Visual Basic nebo jiného programovacího jazyka. Je nutné zadat stejné čtyři argumenty, které jsou vyžadovány v konfiguraci nástroje Enterprise Manager a serveru SQL Server Management Studio.
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
Použití služby SMO ke konfiguraci zdroje dat aplikace Excel jako propojeného serveru
V aplikaci SQL Server 2005 můžete pomocí objektů SMO (SQL Server Management Objects) nakonfigurovat zdroj dat aplikace Excel jako propojený server programově. To lze provést pomocí jazyka Microsoft Visual Basic .NET nebo jiného programovacího jazyka. Je nutné zadat argumenty, které jsou vyžadovány v konfiguraci serveru SQL Server Management Studio. Objektový model SMO rozšiřuje a nahrazuje objektový model SQL-DMO (Distributed Management Objects). Protože je SMO kompatibilní se serverem SQL Server verze 7,0, SQL Server 2000 a SQL Server 2005, můžete také použít SMO pro konfiguraci serveru 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 ClassDotaz na zdroj dat aplikace Excel na propojeném serveru
Jakmile nakonfigurujete zdroj dat aplikace Excel jako propojený server, můžete snadno zadat dotaz na jeho data z nástroje Analýza dotazů nebo z jiné klientské aplikace. Chcete-li například načíst řádky dat uložené v listu List1 souboru aplikace Excel, použije následující kód propojený server nakonfigurovaný pomocí serveru SQL-DMO:
SELECT * FROM XLTEST_DMO...Sheet1$
Pomocí funkce OPENQUERY můžete také zadávat dotazy na server propojených aplikací Excel způsobem "Passthrough", a to následovně:
SELECT * FROM OPENQUERY(XLTEST_DMO, 'SELECT * FROM [Sheet1$]')
První argument, který OPENQUERY očekává, je název propojeného serveru. Pro názvy listů jsou vyžadovány oddělovače, jak je ukázáno výše. Seznam všech tabulek, které jsou k dispozici na propojeném serveru aplikace Excel, můžete také získat pomocí následujícího dotazu:
EXECUTE SP_TABLES_EX 'XLTEST_DMO'
Dotazování na zdroj dat aplikace Excel pomocí distribuovaných dotazů
Můžete použít distribuované dotazy serveru SQL Server a funkci OPENDATASOURCE nebo OPENROWSET k dotazování zřídka používaných zdrojů dat aplikace Excel ad hoc. Poznámka: Používáte-li SQL Server 2005, ujistěte se, že jste povolili možnost ad hoc – distribuované dotazy pomocí konfigurace povrchových oblastí serveru SQL Server, jako v následujícím příkladu:
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=c:\book1.xls;Extended Properties=Excel 8.0')...Sheet1$Všimněte si, že OPENROWSET používá neobvyklá syntaxi pro druhý argument ("řetězec Provider"):
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\book1.xls', Sheet1$)Syntaxe, kterou může vývojář rozhraní ADO (ActiveX Data Objects) použít pro druhý argument ("Provider String") s OPENROWSET:
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Data Source=c:\book1.xls;Extended Properties=Excel 8.0', Sheet1$)Tato syntaxe vyvolává z zprostředkovatele Jet následující chybu:
Nelze najít instalovatelnou metodu ISAM.
Poznámka: K této chybě také dojde, pokud zadáte zdrojdat místo datového zdroje. Například následující argument je nesprávný:
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'DataSource=c:\book1.xls;Extended Properties=Excel 8.0', Sheet1$) Odkazy
Vzhledem k tomu, že propojené servery a distribuované dotazy serveru SQL Server používají zprostředkovatele OLE DB, jsou zde použity obecné pokyny a upozornění týkající se použití objektů ADO s aplikací Excel. Další informace získáte v následujícím článku znalostní báze Microsoft Knowledge Base:
257819 jak používat objekty ADO s daty aplikace Excel z aplikace Visual Basic nebo VBADalší informace o objektech pro správu serveru SQL Server naleznete na následujícím webu služby MSDN (Microsoft Developer Network):
http://msdn2.microsoft.com/en-us/library/ms162169(ide).aspxDalší informace o povolení možnosti pro distribuované dotazy ad hoc naleznete na následujícím webu služby MSDN:
