Использование Excel со связанными серверами SQL Server и распределенными запросами

Переводы статьи Переводы статьи
Код статьи: 306397
Развернуть все | Свернуть все

В этой статье

Аннотация

Microsoft SQL Server поддерживает подключения к другим источникам данных OLE DB (как постоянные, так и прямые). При наличии постоянного подключения сервер называется связанным. Прямое подключение устанавливается для отправки одного запроса (распределенного запроса).

Одним из типов источников данных OLE DB, которые можно запрашивать через SQL Server подобным образом, являются книги Microsoft Excel. В этой статье описан синтаксис, который необходимо использовать при настройке источника данных Excel в качестве связанного сервера, а также синтаксис распределенного запроса к источнику данных Excel.

Дополнительная информация

Запрос источника данных Excel на связанном сервере

Для настройки источника данных Excel в качестве связанного сервера SQL Server можно использовать SQL Server Management Studio, SQL Server Enterprise Manager, системную хранимую процедуру, SQL-DMO или SMO. (Объекты SMO поддерживаются только в Microsoft SQL Server 2005.) В каждом случае необходимо задать следующие четыре свойства:
  • имя, которое необходимо назначить связанному серверу;
  • поставщика OLE DB, который будет использоваться для подключения;
  • источник данных (полный путь и имя файла книги Excel);
  • строку поставщика, которая указывает на то, что конечным объектом является книга Excel. По умолчанию поставщик Jet ожидает базу данных Access.
Кроме того, системная хранимая процедура sp_addlinkedserver ожидает свойство @srvproduct, которое может быть любым строковым значением.

Примечание. При использовании SQL Server 2005 необходимо задать непустое значение для свойства Название продукта в SQL Server Management Studio или для свойства @srvproduct в хранимой процедуре для источника данных Excel.

Настройка источника данных Excel в качестве связанного сервера с помощью SQL Server Management Studio или SQL Server Enterprise Manager

SQL Server Management Studio (SQL Server 2005)
  1. В SQL Server Management Studio разверните узел Объекты сервера в обозревателе объектов.
  2. Щелкните правой кнопкой мыши узел Связанные серверы и выберите команду Создание связанного сервера.
  3. В левой области откройте страницу Общие и выполните указанные ниже действия.
    1. В первом текстовом поле введите любое имя для связанного сервера.
    2. Выберите параметр Другой источник данных.
    3. В списке Поставщик выберите пункт Microsoft Jet 4.0 OLE DB Provider.
    4. В поле Название продукта введите Excel в качестве имени источника данных OLE DB.
    5. В поле Источник данных введите полный путь и имя файла Excel.
    6. В поле Строка поставщика введите Excel 8.0 для книги Excel 2002, Excel 2000 или Excel 97.
    7. Чтобы создать связанный сервер, нажмите кнопку ОК.
Примечание. В SQL Server Management Studio нельзя развернуть имя нового связанного сервера, чтобы просмотреть список объектов, которые он содержит.
Enterprise Manager (SQL Server 2000)
  1. В Enterprise Manager разверните папку Security.
  2. Щелкните правой кнопкой мыши узел Linked Servers и выберите команду New linked server.
  3. На вкладке General выполните указанные ниже действия.
    1. В первом текстовом поле введите любое имя для связанного сервера.
    2. В поле Server type выберите вариант Other data source.
    3. В списке Provider name выберите пункт Microsoft Jet 4.0 OLE DB Provider.
    4. В поле Data source введите полный путь и имя файла Excel.
    5. В поле Provider string введите Excel 8.0 для книги Excel 2002, Excel 2000 или Excel 97.
    6. Чтобы создать связанный сервер, нажмите кнопку ОК.
  4. Щелкните имя связанного сервера, чтобы развернуть список объектов, которые он содержит.
  5. Под именем связанного сервера нажмите кнопку Tables. В правой области появятся книги и именованные диапазоны.

Настройка источника данных Excel в качестве связанного сервера с помощью хранимой процедуры

Для настройки источника данных Excel в качестве связанного сервера также можно использовать системную хранимую процедуру 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
				
Как указано выше, для хранимой процедуры требуется дополнительное произвольное строковое значение для аргумента @srvproduct, которое соответствует значению параметра "Название продукта" в Enterprise Manager и SQL Server Management Studio. Аргументы @location и @catalog не используются.

Настройка источника данных Excel в качестве связанного сервера с помощью SQL-DMO

Для настройки источника данных Excel в качестве связанного сервера с помощью языка программирования (например, Microsoft Visual Basic) можно использовать SQL-DMO. Необходимо указать те же четыре аргумента, которые требуются при настройке через Enterprise Manager и 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
				

Настройка источника данных Excel в качестве связанного сервера с помощью SMO

В SQL Server 2005 для настройки источника данных Excel в качестве связанного сервера программным способом можно использовать SMO. Для этого применяется Microsoft Visual Basic .NET или другой язык программирования. Необходимо указать те же аргументы, которые требуются при настройке через SQL Server Management Studio. Объектная модель SMO расширяет и заменяет объектную модель SQL-DMO. Так как модель SMO совместима с SQL Server 7.0, SQL Server 2000 и SQL Server 2005, ее также можно использовать для настройки 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("имя_сервера\имя_экземпляра", "имя_пользователя", "пароль")
        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("Создан связанный сервер.")
        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

Запрос источника данных Excel на связанном сервере

После настройки источника данных Excel в качестве связанного сервера можно запрашивать его данные с помощью Query Analyzer или другого клиентского приложения. Например, чтобы получить строки данных, которые хранятся на листе Sheet1 файла Excel, используйте через SQL-DMO следующий код для настроенного связанного сервера:
SELECT * FROM XLTEST_DMO...Sheet1$
				
Кроме того, можно использовать OPENQUERY для "транзитного" запроса связанного сервера Excel:
SELECT * FROM OPENQUERY(XLTEST_DMO, 'SELECT * FROM [Sheet1$]')
				
Первый аргумент, который требуется OPENQUERY, — это имя связанного сервера. Чтобы указать имена листов, используйте разделители, как показано выше.

Кроме того, можно получить список всех таблиц, доступных на связанном сервере Excel, с помощью следующего запроса:
EXECUTE SP_TABLES_EX 'XLTEST_DMO'
				

Опрос источника данных Excel с помощью распределенных запросов

Для опроса нечасто используемых источников данных Excel можно использовать распределенные запросы SQL Server и функцию OPENDATASOURCE или OPENROWSET.

Примечание. При работе с SQL Server 2005 включите параметр Ad Hoc Distributed Queries с помощью средства настройки контактной зоны, как показано в примере ниже.
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
   'Data Source=c:\book1.xls;Extended Properties=Excel 8.0')...Sheet1$
				
Обратите внимание на необычный синтаксис второго аргумента OPENROWSET ("Строка поставщика"):
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
   'Excel 8.0;Database=c:\book1.xls', Sheet1$)
				
Синтаксис, привычный для разработчиков ADO, выглядит следующим образом:
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
   'Data Source=c:\book1.xls;Extended Properties=Excel 8.0', Sheet1$)
				
Этот синтаксис вызывает следующую ошибку поставщика Jet:
Невозможно найти устанавливаемый ISAM.
Примечание. Эта же ошибка возникает при вводе DataSource вместо Data Source. Например, следующий аргумент является неправильным:
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'DataSource=c:\book1.xls;Extended Properties=Excel 8.0', Sheet1$) 
				

Ссылки

Так как для связанных серверов SQL Server и распределенных запросов используется поставщик OLE DB, учитывайте общие рекомендации и предупреждения, которые относятся к применению ADO с Excel. Дополнительные сведения см. в следующей статье базы знаний Майкрософт:
257819 Использование ADO с данными Excel из Visual Basic или VBA (Эта ссылка может указывать на содержимое полностью или частично на английском языке)
Дополнительные сведения о SMO см. на следующем веб-узле MSDN:
http://msdn2.microsoft.com/en-us/library/ms162169(ide).aspx
Дополнительные сведения о включении параметра Ad Hoc Distributed Queries см. на следующем веб-узле MSDN:
http://msdn2.microsoft.com/en-us/library/ms189978(ide).aspx

Свойства

Код статьи: 306397 - Последний отзыв: 15 февраля 2011 г. - Revision: 6.4
Ключевые слова: 
kbsqlmanagementtools kbdatabase kbhowto kbjet KB306397

Отправить отзыв

 

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