Używanie programu Excel z serwerami połączonymi SQL Server i kwerendami rozproszonymi

Tłumaczenia artykułów Tłumaczenia artykułów
Numer ID artykułu: 306397
Rozwiń wszystko | Zwiń wszystko

Na tej stronie

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)
  1. W programie SQL Server Management Studio rozwiń listę Server Objects (Obiekty serwera) w menu Object Explorer (Eksplorator obiektów).
  2. Kliknij prawym przyciskiem myszy węzeł Linked Servers (Połączone serwery), a następnie kliknij polecenie New Linked Server (Nowy serwer połączony).
  3. W lewym okienku wybierz stronę General (Ogólne), a następnie wykonaj następujące kroki:
    1. W pierwszym polu tekstowym wpisz dowolną nazwę połączonego serwera.
    2. Zaznacz opcję Other data source (Inne źródło danych).
    3. Z listy Provider (Dostawca) wybierz pozycję Microsoft Jet 4.0 OLE DB Provider.
    4. W polu Product name (Nazwa produktu) wpisz Excel jako nazwę źródła danych OLE DB.
    5. W polu Data source (Źródło danych) wpisz pełną ścieżkę i nazwę pliku programu Excel.
    6. W polu Provider string (Ciąg dostawcy) wpisz Excel 8.0 w przypadku skoroszytu programów Excel 2002, Excel 2000 lub Excel 97.
    7. 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)
  1. W programie Enterprise Manager kliknij folder Security (Bezpieczeństwo), aby rozwinąć jego zawartość.
  2. Kliknij prawym przyciskiem myszy węzeł Linked Servers (Połączone serwery), a następnie kliknij polecenie New linked server (Nowy serwer połączony).
  3. Na karcie General (Ogólne) wykonaj następujące kroki:
    1. W pierwszym polu tekstowym wpisz dowolną nazwę połączonego serwera.
    2. W polu Server type (Typ serwera) kliknij opcję Other data source (Inne źródło danych).
    3. Z listy Provider name (Nazwa dostawcy) wybierz pozycję Microsoft Jet 4.0 OLE DB Provider.
    4. W polu Data source (Źródło danych) wpisz pełną ścieżkę i nazwę pliku programu Excel.
    5. W polu Provider string (Ciąg dostawcy) wpisz Excel 8.0 w przypadku skoroszytu programów Excel 2002, Excel 2000 lub Excel 97.
    6. Kliknij przycisk OK, aby utworzyć nowy serwer połączony.
  4. Kliknij nazwę nowego serwera połączonego, aby rozwinąć listę jego obiektów.
  5. 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).aspx
Aby 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:
http://msdn2.microsoft.com/en-us/library/ms189978(ide).aspx

Właściwości

Numer ID artykułu: 306397 - Ostatnia weryfikacja: 15 lutego 2011 - Weryfikacja: 6.6
Słowa kluczowe: 
kbsqlmanagementtools kbdatabase kbhowto kbjet KB306397

Przekaż opinię

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com