Аннотация
Microsoft SQL Server поддерживает подключения к другим источникам данных OLE DB (как постоянные, так и прямые). При наличии постоянного подключения сервер называется связанным. Прямое подключение устанавливается для отправки одного запроса (распределенного запроса).
Одним из типов источников данных OLE DB, которые можно запрашивать через SQL Server подобным образом, являются книги Microsoft Excel. В этой статье описан синтаксис, который необходимо использовать при настройке источника данных Excel в качестве связанного сервера, а также синтаксис распределенного запроса к источнику данных Excel.
Дополнительная информация
Запрос источника данных Excel на связанном сервере
Вы можете использовать SQL Server Management Studio или Enterprise Manager, хранимую в системе процедуру, SQL-DMO (Объекты распределенного управления) или SMO (Управляющие объекты SQL Server) для настройки источника данных Excel в качестве связанного сервера SQL Server. (Объекты SMO поддерживаются только в Microsoft SQL Server 2005.) В каждом случае необходимо задать следующие четыре свойства:
-
Имя, которое необходимо использовать для связанного сервера.
-
Поставщик OLE DB, который будет использоваться для подключения.
-
Источник данных или полное имя пути и файла для рабочей книги Excel.
-
Строка провайдера, которая идентифицирует цель как рабочую книгу Excel. По умолчанию поставщик Jet ожидает базу данных Access.
Хранимая в системе процедура sp_addlinkedserver также требует свойство @srvproduct, которое может быть любым строковым значением.
Заметка Если вы используете SQL Server 2005, то для свойства Имя продукта в SQL Server Management Studio или для свойства @srvproduct в хранимой процедуре для источника данных Excel необходимо указать значение, которое не должно быть пустым.
Использование SQL Server Management Studio или Enterprise Manager для настройки источника данных Excel в качестве связанного сервера
SQL Server Management Studio (SQL Server 2005)
-
В SQL Server Management Studio разверните Серверные объекты в Обозреватель объектов.
-
Щелкните правой кнопкой мыши Связанные серверы, а затем щелкните Новый связанный сервер.
-
В левой панели выберите страницу Общие, а затем выполните следующие шаги:
-
В первом текстовом поле введите любое имя для связанного сервера.
-
Выберите опцию Другой источник данных.
-
В списке Поставщик выберите Microsoft Jet 4.0 OLE DB Provider.
-
В поле Имя продукта введите Excel для имени источника данных OLE DB.
-
В поле Источник данных введите полный путь и имя файла Excel.
-
В поле Строка поставщика введите Excel 8.0 для рабочей книги Excel 2002, Excel 2000 или Excel 97.
-
Нажмите OK, чтобы создать новый связанный сервер.
-
Примечание В SQL Server Management Studio невозможно развернуть имя нового связанного сервера для просмотра списка объектов, содержащихся на сервере.
Enterprise Manager (SQL Server 2000)
-
В менеджере Enterprise Manager щелкните, чтобы развернуть папку Безопасность.
-
Щелкните правой кнопкой мыши Связанные серверы, а затем щелкните Новый связанный сервер.
-
На вкладке Общие выполните следующие действия:
-
В первом текстовом поле введите любое имя для связанного сервера.
-
В поле Тип сервера нажмите Другой источник данных.
-
В списке Имя поставщика нажмите кнопку Microsoft Jet 4.0 OLE DB Provider.
-
В поле Источник данных введите полный путь и имя файла Excel.
-
В поле Строка поставщика введите Excel 8.0 для рабочей книги Excel 2002, Excel 2000 или Excel 97.
-
Нажмите OK, чтобы создать новый связанный сервер.
-
-
Щелкните имя связанного сервера, чтобы развернуть список объектов, которые он содержит.
-
Под новым именем связанного сервера нажмите Таблицы. В правой области появятся книги и именованные диапазоны.
Использование хранимой процедуры для настройки источника данных Excel в качестве связанного сервера
Вы также можете использовать хранимую в системе процедуру sp_addlinkedserver для настройки источника данных Excel в качестве связанного сервера:
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 не используются.
Использование SQL-DMO для настройки источника данных Excel в качестве связанного сервера
Объекты распределенного управления SQL можно использовать для настройки источника данных Excel в качестве связанного сервера программно с использованием Microsoft Visual Basic или другого языка программирования. Необходимо указать те же четыре аргумента, которые требуются при настройке через 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
Использование SMO для настройки источника данных Excel в качестве связанного сервера
В SQL Server 2005 можно использовать управляющие объекты SQL Server (SMO) для программной настройки источника данных Excel в качестве связанного сервера. Для этого применяется 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("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
Запрос источника данных 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 с помощью распределенных запросов
Можно использовать распределенные запросы SQL Server и функцию OPENDATASOURCE или OPENROWSET для специальных запросов к редко обращающимся источникам данных Excel.
Заметка Если вы используете SQL Server 2005, убедитесь, что вы включили опцию Ad Hoc Distributed Queries, используя Настройка контактной зоны SQL Server, как в следующем примере:
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.
Примечание Эта ошибка также возникает, если вместо ИсточникДанных ввести Источник данных. Например, следующий аргумент является неправильным:
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 с данными из Visual Basic или VBA в Excel.
Для получения дополнительной информации об управляющих объектах SQL Server (SMO) посетите следующий веб-сайт MSDN:
http://msdn2.microsoft.com/ru-ru/library/ms162169(ide).aspxДля получения дополнительной информации о том, как включить опцию Ad Hoc Distributed Queries, посетите следующий веб-сайт MSDN:
