Подключение внешних баз данных

Сценарии Октелл обращаются к внешним базам компонентом «Запрос в базу данных» — через ADO, OLE или ODBC. Для разовых обращений этого достаточно. Но когда внешняя база используется постоянно, а запросов и хранимых процедур много, каждый раз указывать строку подключения в компоненте неудобно и накладно.

Процедура состоит из двух шагов: зарегистрировать связанный сервер на MS SQL Server и написать нужные запросы на T-SQL. MS SQL Server поддерживает все распространённые форматы хранения данных.

Провайдеры OLE DB

Существует ещё несколько провайдеров для узких задач и редко используемых баз.

Регистрация связанного сервера

Двумя способами: через интерфейс SQL Server Management Studio — ветка «Объекты сервера → Связанные серверы», — либо системными хранимыми процедурами sp_addlinkedserver и sp_addlinkedsrvlogin.

Обращение к таблицам

Таблицы связанного сервера указываются полным именем из четырёх частей, разделённых точками: сервер, база, схема, таблица. Если схема пропущена, используется схема по умолчанию — для тех СУБД, что работают со схемами.

Select * From [ACCESSSAMPLE]...[Table_Users]
Select * From OrclDB..MARY.SALES

Для источников через ODBC применяется openquery, передающий запрос на исполнение самой внешней СУБД:

select * from openquery (MYSQL, 'select * from table_name')

MS Access

Через интерфейс: подключиться к SQL Server с базами Октелл, открыть Security/Linked Servers, зарегистрировать новый связанный сервер. На вкладке General указать название сервера, провайдера Microsoft Jet Ole DB Provider и полный путь к файлу *.MDB. Если СУБД требует аутентификации — задать её на вкладке Security. При верных данных отобразится список таблиц и представлений подключённой базы.

То же самое запросом:

EXEC sp_addlinkedserver 'AccessSample', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
                        'C:\Data\db1.mdb', NULL, NULL

EXEC sp_addlinkedsrvlogin 'AccessSample', false, NULL, NULL

Oracle

1. Убедитесь, что версия клиентского ПО Oracle на сервере с SQL Server не ниже требуемой провайдером: Oracle Client Software Support File 7.3.3.4.0 или новее и SQL*Net 2.3.3.0.4.
2. Зарегистрируйте на этом сервере сетевой псевдоним SQL*Net, ссылающийся на подключаемую базу.
3. Выполните sp_addlinkedserver, указав провайдером MSDAORA, а источником — созданный псевдоним.
4. Выполните sp_addlinkedsrvlogin, чтобы сопоставить логины SQL Server логинам Oracle.

EXEC sp_addlinkedserver 'OrclDB', 'Oracle', 'MSDAORA', 'OracleDB'

MS Excel

Через Microsoft.ACE.OLEDB.12.0

Провайдер подходит и для 32-, и для 64-битных версий SQL Server. Устанавливать удобнее из командной строки с ключом /passive.

Читать данные можно напрямую, без регистрации связанного сервера, — командой OPENROWSET:

select * from OPENROWSET('Microsoft.ACE.OLEDB.12.0',
       'Excel 8.0; HDR=Yes; Database=C:\Sample.xlsx',
       'Select * from [Sheet1$]')

Изменять и добавлять — тоже:

UPDATE OPENROWSET('Microsoft.ACE.OLEDB.12.0',
       'Excel 8.0; HDR=Yes; Database=C:\Sample.xlsx',
       'Select * from [Sheet1$]')
SET id = '123456'
WHERE phone = '...'

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0',
       'Excel 12.0;Database=C:\Sample.xlsx',
       'Select * from [Sheet1$]')
SELECT id, phone, comment from sqltable

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1

sp_configure 'show advanced options', 1;
reconfigure
sp_configure 'Ad Hoc Distributed Queries', 1;
reconfigure

Excel как связанный сервер

exec sp_addlinkedserver @server = 'XlsLnkSrv',
     @srvproduct = 'ACE 12.0',
     @provider = 'Microsoft.ACE.OLEDB.12.0',
     @datasrc = 'C:\Sample.xlsx',
     @provstr = 'Excel 12.0; HDR=Yes'

После обновления обозревателя объектов провайдер появится в ветке «Объекты сервера → Связанные серверы → Поставщики». Читать данные:

select * from openquery (XlsLnkSrv, 'Select * from [Sheet1$]')

Через Microsoft Jet 4.0

Только для 32-битных версий MS SQL Server.

EXEC sp_addlinkedserver 'ExcelSource', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
     'c:\MyData\DistExcl.xls', NULL, 'Excel 8.0'

EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false', NULL, NULL

Select * From [ExcelSource]...[Лист1$]

MySQL

1. Установите провайдер ODBC для MySQL нужной разрядности.
2. Настройте системный DSN: в «Администраторе источников данных ODBC», вкладка «Системный DSN», добавьте источник с драйвером MySQL. Заполните имя DSN, описание, адрес сервера, логин, пароль и подключаемую базу; проверьте соединение кнопкой Test.
3. В SQL Server Management Studio создайте связанный сервер.

На вкладке «Безопасность» выберите «Устанавливать с использованием следующего контекста безопасности» и укажите учётные данные администратора MySQL. На вкладке «Параметры сервера» установите RPC и RPC Out в True.

Firebird

1. Установите сервер Firebird — 32-разрядную версию.
2. Установите провайдер ODBC для Firebird той же разрядности, что и SQL Server.
3. Для 64-битного SQL Server дополнительно нужна 64-разрядная библиотека клиента Firebird fbclient.dll; распакуйте её в отдельную папку.
4. Настройте системный DSN с драйвером «Firebird/InterBase(r) driver».
5. Создайте связанный сервер: поставщик Microsoft OLE DB Provider for ODBC Drivers, источник данных — имя DSN.
6. В ветке «Объекты сервера → Связанные серверы → Поставщики» откройте параметры поставщика MSDASQL и включите «Только нулевой уровень».

Запросы к связанному серверу — по имени, указанному при его создании:

select * from FB...Table

UPDATE FB...Table
SET NAME = 'admin'
WHERE ID = 1

INSERT INTO FB...Table (ID, NAME)
VALUES (1000, 'manager')