Streszczenie
Program Microsoft SQL Server obsługuje połączenia z innymi źródłami danych OLE DB na zasadzie stałej lub ad hoc. Połączenie stałe jest nazywane serwerem połączonym; połączenie ad hoc nawiązywane na potrzeby jednego zapytania jest nazywane kwerendą rozproszoną.
Skoroszyty programu Microsoft Excel są jednym z typów źródeł danych OLE DB, do których można w ten sposób wysyłać kwerendy za pośrednictwem programu SQL Server. W tym artykule opisano składnię konieczną do konfiguracji źródła danych programu Excel jako połączonego serwera, a także składnię, której trzeba użyć w kwerendzie rozproszonej wysyłanej do źródła danych programu Excel.
Więcej informacji
Badanie źródła danych programu Excel na serwerze połączonym
Do konfiguracji źródła danych programu Excel jako połączonego serwera SQL Server można użyć programów SQL Server Management Studio lub Enterprise Manager, procedury zapisanej w systemie, obiektów SQL-DMO (Distributed Management Objects) lub SMO (SQL Server Management Objects). (Obiekty SMO są dostępne tylko w programie Microsoft SQL Server 2005.) We wszystkich tych przypadkach trzeba zawsze ustawić następujące cztery właściwości:
-
Nazwa, która ma być używana dla serwera połączonego.
-
Dostawca OLE DB, który ma być używany dla tego połączenia.
-
Źródło danych lub pełna ścieżka i nazwa pliku dla skoroszytu programu Excel.
-
Ciąg dostawcy identyfikujący cel jako skoroszyt programu Excel. Domyślnie dostawca Jet oczekuje bazy danych Access.
Przechowywana w systemie procedura sp_addlinkedserver także oczekuje właściwości @srvproduct, która może być dowolną wartością ciągu.
Uwaga: W przypadku programu SQL Server 2005 trzeba określić wartość, która nie będzie pusta dla właściwości Product name (Nazwa produktu) w programie SQL Server Management Studio lub @srvproduct w przechowywanej procedurze dla źródła danych programu Excel.
Konfigurowanie źródła danych programu Excel jako połączonego serwera za pomocą programu SQL Server Management Studio lub Enterprise Manager
Program SQL Server Management Studio (SQL Server 2005)
-
W programie SQL Server Management Studio rozwiń listę Server Objects (Obiekty serwera) w menu Object Explorer (Eksplorator obiektów).
-
Kliknij prawym przyciskiem myszy węzeł Linked Servers (Połączone serwery), a następnie kliknij polecenie New Linked Server (Nowy serwer połączony).
-
W lewym okienku wybierz stronę General (Ogólne), a następnie wykonaj następujące kroki:
-
W pierwszym polu tekstowym wpisz dowolną nazwę połączonego serwera.
-
Zaznacz opcję Other data source (Inne źródło danych).
-
Z listy Provider (Dostawca) wybierz pozycję Microsoft Jet 4.0 OLE DB Provider.
-
W polu Product name (Nazwa produktu) wpisz Excel jako nazwę źródła danych OLE DB.
-
W polu Data source (Źródło danych) wpisz pełną ścieżkę i nazwę pliku programu Excel.
-
W polu Provider string (Ciąg dostawcy) wpisz Excel 8.0 w przypadku skoroszytu programów Excel 2002, Excel 2000 lub Excel 97.
-
Kliknij przycisk OK, aby utworzyć nowy serwer połączony.
-
Uwaga: W programie SQL Server Management Studio nie można rozwinąć nazwy nowego serwera połączonego, aby wyświetlić listę jego obiektów.
Program Enterprise Manager (SQL Server 2000)
-
W programie Enterprise Manager kliknij folder Security (Bezpieczeństwo), aby rozwinąć jego zawartość.
-
Kliknij prawym przyciskiem myszy węzeł Linked Servers (Połączone serwery), a następnie kliknij polecenie New linked server (Nowy serwer połączony).
-
Na karcie General (Ogólne) wykonaj następujące kroki:
-
W pierwszym polu tekstowym wpisz dowolną nazwę połączonego serwera.
-
W polu Server type (Typ serwera) kliknij opcję Other data source (Inne źródło danych).
-
Z listy Provider name (Nazwa dostawcy) wybierz pozycję Microsoft Jet 4.0 OLE DB Provider.
-
W polu Data source (Źródło danych) wpisz pełną ścieżkę i nazwę pliku programu Excel.
-
W polu Provider string (Ciąg dostawcy) wpisz Excel 8.0 w przypadku skoroszytu programów Excel 2002, Excel 2000 lub Excel 97.
-
Kliknij przycisk OK, aby utworzyć nowy serwer połączony.
-
-
Kliknij nazwę nowego serwera połączonego, aby rozwinąć listę jego obiektów.
-
Pod nazwą nowego serwera połączonego kliknij opcję Tables (Tabele). Zwróć uwagę, że skoroszyty i nazwane zasięgi są widoczne w prawym okienku.
Konfigurowanie źródła danych programu Excel jako połączonego serwera za pomocą zapisanej procedury
Do konfiguracji źródła danych jako połączonego serwera można wykorzystać zapisaną w systemie procedurę sp_addlinkedserver:
DECLARE @RC int
DECLARE @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 values
SET @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 już wspomniano, zapisana procedura wymaga dodatkowej, umownej wartości ciągu dla argumentu @srvproduct, który jest wyświetlany jako „Product name” (Nazwa produktu) w konfiguracji programów Enterprise Manager i SQL Server Management Studio. Argumenty @location i @catalog nie są używane.
Konfigurowanie źródła danych programu Excel jako połączonego serwera za pomocą obiektów SQL-DMO
Do programowej konfiguracji źródła danych programu Excel jako serwera połączonego za pomocą programu Microsoft Visual Basic lub innego języka programowania można użyć obiektów SQL-DMO (Distributed Management Objects). Trzeba podać te same cztery argumenty wymagane przy konfigurowaniu za pomocą programów Enterprise Manager i 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.Close
End Sub
Konfigurowanie źródła danych programu Excel jako połączonego serwera za pomocą obiektów SMO
Do programowego konfigurowania źródła danych programu Excel jako serwera połączonego w programie SQL Server 2005 można użyć obiektów SMO. W tym celu można użyć programu Microsoft Visual Basic .NET lub innego języka programowania. Trzeba podać argumenty wymagane przy konfigurowaniu za pomocą programu SQL Server Management Studio. Model obiektu SMO rozszerza i zastępuje model obiektu SQL-DMO. Ponieważ obiekty SMO są zgodne z programami SQL Server w wersji 7.0, SQL Server 2000 oraz SQL Server 2005, można ich także używać do konfiguracji programu SQL Server 2000.
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common
Public 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 Sub
End Class
Badanie źródła danych programu Excel na serwerze połączonym
Po skonfigurowaniu źródła danych programu Excel jako serwera połączonego można łatwo wysyłać kwerendy za pomocą programu Query Analyzer lub innej aplikacji. Na przykład, aby pobrać wiersze danych przechowywane w Arkuszu 1 pliku programu Excel, następujący kod używa serwera połączonego skonfigurowanego za pomocą obiektu SQL-DMO:
SELECT * FROM XLTEST_DMO...Sheet1$
Można także użyć opcji OPENQUERY do wysyłania kwerendy do serwera połączonego programu Excel w trybie „przekazywania” w następujący sposób:
SELECT * FROM OPENQUERY(XLTEST_DMO, 'SELECT * FROM [Sheet1$]')
Pierwszym argumentem oczekiwanym przez OPENQUERY jest nazwa serwera połączonego. W przypadku nazw skoroszytów potrzebne są ograniczniki, jak wyżej.
Można także uzyskać listę wszystkich tabel dostępnych w serwerze połączonym programu Excel za pomocą następującej kwerendy:
EXECUTE SP_TABLES_EX 'XLTEST_DMO'
Badanie źródła danych programu Excel za pomocą kwerend rozproszonych
W celu wysyłania kwerend do rzadko używanych źródeł danych na zasadzie ad-hoc można użyć funkcji OPENDATASOURCE lub OPENROWSET.
Uwaga: W przypadku programu SQL Server 2005 trzeba sprawdzić, czy opcja Ad Hoc Distributed Queries (Kwerendy rozproszone ad-hoc) jest włączona. Można to zrobić za pomocą narzędzia SQL Server Surface Area Configuration, jak w przykładzie poniżej:
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\book1.xls;Extended Properties=Excel 8.0')...Sheet1$
Warto pamiętać, że funkcja OPENROWSET używa nietypowej składni w drugim argumencie (Provider String) (Ciąg dostawcy):
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\book1.xls', Sheet1$)
Składnia, której użycia dla drugiego argumentu (Provider String) (Ciąg dostawcy) za pomocą opcji OPENROWSET może spodziewać się deweloper obiektów ADO (ActiveX Data Objects):
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\book1.xls;Extended Properties=Excel 8.0', Sheet1$)
Ta składania powoduje następujący błąd dostawcy Jet:
Could not find installable ISAM. (Nie można znaleźć usługi ISAM nadającej się do zainstalowania.)
Uwaga: Ten błąd występuje także po wpisaniu ciągu DataSource zamiast Data Source. Na przykład następujący argument jest nieprawidłowy:
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'DataSource=c:\book1.xls;Extended Properties=Excel 8.0', Sheet1$)
Materiały referencyjne
Ponieważ serwery połączone SQL Server i kwerendy rozproszone korzystają z dostawcy OLE DB, mają tu zastosowanie ogólne wytyczne i ostrzeżenia dotyczące używania obiektów ADO z programem Excel.
Aby uzyskać więcej informacji, kliknij następujący numer artykułu w celu wyświetlenia tego artykułu z bazy wiedzy Microsoft Knowledge Base:
257819 Używanie obiektów ADO z danymi programu Excel z programów Visual Basic lub VBA
Aby uzyskać więcej informacji dotyczących Obiektów SQL-SMO (Server Management Objects), odwiedź następującą witrynę firmy Microsoft Developer Network (MSDN) w sieci Web:
http://msdn2.microsoft.com/en-us/library/ms162169(ide).aspxAby uzyskać więcej informacji dotyczących sposobu włączania opcji Ad Hoc Distributed Queries (Kwerendy rozproszone ad hoc), odwiedź następującą witrynę firmy Microsoft Developer Network (MSDN) w sieci Web:
