Настройка связанного сервера с Oracle в Microsoft SQL Server

В Microsoft SQL Server есть возможность обращения к различным источникам данных, которые расположены вне SQL сервера, это возможно благодаря технологии связанных серверов и сегодня мы с Вами рассмотрим пример настройки связанного сервера с СУБД Oracle.

Связанный сервер Oracle

Подробно о том, что такое связанный сервер, а также простые примеры настройки связанных серверов мы с Вами рассматривали в материале – Связанные серверы в MS SQL Server 2014.

Сейчас как я уже сказал, мы будем настраивать связанный сервер, источником данных которого будет выступать СУБД Oracle.

Примечание! Для примера я буду использовать Microsoft SQL Server 2016 Express, а версия Oracle у меня Oracle Database Express Edition 11g.

Прежде чем начать хотелось бы сказать, что для настройки связанных серверов используются так называемые «Провайдеры» или «Поставщики», которых на самом деле много. Для связи с Oracle существует несколько поставщиков, например:

  • Microsoft OLE DB Provider for Oracle (MSDAORA) – как не трудно догадаться его разработчиком является компания Microsoft;
  • Oracle Provider for OLE DB (OraOLEDB.Oracle) – данный провайдер разработала сама компания Oracle для связи со своей СУБД.

Все рекомендуют использовать провайдер от Oracle, в том числе и я, основываясь на собственном опыте. Так как OraOLEDB.Oracle более оптимизирован для работы с Oracle, например, как-то раз при использовании провайдера MSDAORA текстовые данные на русском языке (VARCHAR2) с сервера Oracle приходили мне в виде «?????», после недолгого шаманства пришлось сменить провайдера.

Поэтому сегодня мы будем рассматривать пример настройки связанного сервера с использованием Oracle Provider for OLE DB (OraOLEDB.Oracle) от компании Oracle.

Практически для любого поставщика требуются какие-то инструменты, чтобы он функционировал, в том числе и для OraOLEDB.Oracle.

Для того чтобы провайдер OraOLEDB.Oracle работал его необходимо установить, предварительно скачав с официального сайта Oracle.

Как скачать Oracle Provider for OLE DB (OraOLEDB.Oracle)?

Oracle Provider for OLE DB на текущий момент распространяется в составе инструмента для разработчиков ODAC.

Oracle Data Access Components (ODAC) – это набор компонентов для разработчиков, который предоставляет им доступ к данным Oracle.

Чтобы его скачать, необходимо зарегистрироваться на сайте Oracle или если Вы уже зарегистрированы, то войти в аккаунт. Затем переходите на страницу загрузки ODAC вот она.

Потом в зависимости от Вашей системы (т.е. на которой установлен MS SQL Server), Вы выбираете 32 битную версию или 64 битную. У меня система 64 битная, поэтому я нажимаю на ссылку 64-bit ODAC Downloads.

Скриншот 1

После перехода на следующую страницу сразу соглашаетесь с условиями лицензионного соглашения, т.е. отмечаете пункт «Accept License Agreement».

Скриншот 2

Далее Вы выбираете версию ODAC, которая соответствует версии СУБД Oracle.

При этом название файлов с префиксом «Xcopy» означает, что установка будет осуществляться посредством пакетных файлов (батников). Если такого префикса нет, то в данном архиве присутствует некий дистрибутив с графическим интерфейсом.

Итак, как я уже сказал у меня версия Oracle Express Edition 11g, поэтому я выбираю 64-bit ODAC 11.2 Release 5 (11.2.0.3.20) for Windows x64 – это вариант с установкой через графический интерфейс.

Скриншот 3

В итоге у меня загрузился файл ODAC1120320_x64.zip

Установка Oracle Data Access Components (ODAC)

Распаковываем архив и запускам файл setup.exe.

Шаг 1

Запустится программа установки ODAC, на первом окне мы нажимаем «Next».

Скриншот 4

Шаг 2

Затем оставляем по умолчанию, т.е. нам нужна установка клиентской части, жмем «Next».

Скриншот 5

Шаг 3

Далее, если хотите, можете указать каталог для установки ODAC, я предварительно создал папку «OracleOleDB» на диске C специально для этих целей, поэтому я ее и выбираю, жмем «Next».

Скриншот 6

Шаг 4

Теперь нам необходимо выбрать компоненты, которые мы хотим установить, в нашем случае нам нужен только Oracle Provider for OLE DB и, конечно же, Oracle Instant Client, с остальных компонентов снимаем галочку и жмем «Next».

Скриншот 7

Шаг 5

Проверяем все параметры установки и жмем «Install».

Скриншот 8

В итоге начнется процесс установки.

Скриншот 9

Он будет завершен, когда появится следующее сообщение, жмем «Exit».

Скриншот 10

Шаг 6

После того как установился провайдер Oracle Provider for OLE DB нам необходимо в каталог «C:\OracleOleDB\product\11.2.0\client_1\Network\Admin» (в моем случае) скопировать файл tnsnames.ora с сервера Oracle (из каталога …\network\ADMIN) или создать самим такой файлик и вписать в него настройки подключения, которые нам сообщит администратор Oracle, например, у меня для Express Edition они вот такие.

   
       XE =
          (DESCRIPTION =
                (ADDRESS = (PROTOCOL = TCP)(HOST = TestComp)(PORT = 1521))
                (CONNECT_DATA =
                  (SERVER = DEDICATED)
                  (SERVICE_NAME = XE)
                )
          )

        EXTPROC_CONNECTION_DATA =
          (DESCRIPTION =
                (ADDRESS_LIST =
                  (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
                )
                (CONNECT_DATA =
                  (SID = PLSExtProc)
                  (PRESENTATION = RO)
                )
          )

        ORACLR_CONNECTION_DATA = 
          (DESCRIPTION = 
                (ADDRESS_LIST = 
                  (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) 
                ) 
                (CONNECT_DATA = 
                  (SID = CLRExtProc) 
                  (PRESENTATION = RO) 
                ) 
          )

В данном каталоге есть папка Sample, в которой есть пример заполнения файла tnsnames.ora.

После установки Oracle Provider for OLE DB необходимо перезагрузить сервер.

Проверка работы провайдера OraOLEDB.Oracle

Перед тем как переходить к созданию связанного сервера на Microsoft SQL Server необходимо проверить правильно ли мы установили провайдера. Это можно сделать следующим образом. Создайте на рабочем столе простой текстовый файл, например, TestConnect.txt, затем измените его расширение на TestConnect.udl

Запустите его, и у Вас откроется окно «Свойства канала передачи данных». Далее Вам необходимо на вкладке «Поставщик данных» выбрать поставщика, т.е. в нашем случае это «Oracle Provider for OLE DB» и нажать «Далее».

Скриншот 11

После чего Вы перейдете на вкладку «Соединение», где нужно заполнить параметры подключения:

  • Источник данных — адрес сервера Oracle;
  • Пользователь – это пользователь для подключения к СУБД Oracle;
  • Пароль – соответственно пароль для подключения к СУБД Oracle.

И для проверки связи нажимаем «Проверить соединение».

Скриншот 12

Скриншот 13

Если у Вас появилось сообщение «Проверка соединения выполнена», то можете переходить к созданию связанного сервера, если нет, то у Вас что-то с провайдером или с сервером Oracle.

Создание связанного сервера с использованием провайдера OraOLEDB.Oracle

Для того чтобы создать связанный сервер в Microsoft SQL Server запустите среду Management Studio.

Сначала давайте у провайдера изменим один параметр под названием «AllowInProcess» или «Допускать в ходе процесса», так как этого требует провайдер OraOLEDB.Oracle. В остальных случаях не рекомендуется использовать данную опцию, так как в этом случае если произойдет сбой провайдера, то и весь SQL сервер даст сбой.

Для этого в обозревателе объектов найдите контейнер «Объекты сервера -> Связанные серверы-> Поставщики», откройте его и найдите наш провайдер OraOLEDB.Oracle. Щелкните правой кнопкой мыши и откройте «Свойства».

Скриншот 14

Отметьте вышеуказанный параметр, т.е. «Допускать в ходе процесса» и нажмите «ОК».

Скриншот 15

Теперь переходим непосредственно к созданию так называемого Linked-сервера.

Щелкаем правой кнопкой мыши по контейнеру «Связанные серверы» и выбираем «Создать связанный сервер».

Скриншот 16

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

  • Связанный сервер – имя сервера, т.е. название объекта к которому мы будем обращаться из своих SQL инструкций;
  • Поставщик – провайдер, т.е. Oracle Provider for OLE DB;
  • Название продукта – Oracle;
  • Источник данных – это SID, к которому мы будем подключаться на сервере Oracle (должен быть прописан в файле TNSNAMES.ORA);

Скриншот 17

Потом переходим на вкладку «Безопасность», выбираем пункт «Устанавливать с использованием следующего контекста безопасности» и вводим логин и пароль от СУБД Oracle. Жмем «ОК».

Скриншот 18

Все готово, связанный сервер мы создали, и он у нас отобразился в списке связанных серверов в Management Studio. Теперь мы можем обращаться (посылать запросы) к этому связанному серверу, например, у меня на сервере Oracle есть таблица ORACLETABLE, которая содержит просто какой-то список товаров, и для того чтобы осуществить выборку данных из этой таблицы с помощью Management Studio SQL сервера, можно использовать следующие запросы.

   
        SELECT * FROM ORACLEDB..ORACLEUSER.ORACLETABLE
        --Или так
        SELECT * FROM OPENQUERY(ORACLEDB,
                                                        'SELECT * FROM ORACLETABLE')

Скриншот 19

Если Вы предпочитаете пользоваться SQL инструкциями, то ниже представлена инструкция, которая делает ровно то же самое что и мы чуть ранее в графическом интерфейсе SSMS.

   
        USE [master]
        GO
        EXEC sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1
        GO
        EXEC sp_addlinkedserver @server = N'ORACLEDB', 
                                                        @srvproduct=N'Oracle', 
                                                        @provider=N'OraOLEDB.Oracle', 
                                                        @datasrc=N'XE'
        GO
        EXEC sp_addlinkedsrvlogin @rmtsrvname = N'ORACLEDB', 
                                                          @locallogin = NULL , 
                                                          @useself = N'False', 
                                                          @rmtuser = N'ORACLEUSER', 
                                                          @rmtpassword = N'Pa$$w0rd'
        GO 
        SELECT * FROM OPENQUERY(ORACLEDB,
                                                        'SELECT * FROM ORACLETABLE')

Устранение возможных ошибок при создании связанного сервера с Oracle в Microsoft SQL Server

Ошибка 7302

«Не удалось создать экземпляр поставщика OLE DB «OraOLEDB.Oracle» для связанного сервера …»

Возможно, отсутствует (или некорректный) путь к каталогу с Instant Client (каталог в который Вы установили ODAC) в системной переменной PATH. Необходимо его прописать и перезапустить SQL сервер. Также возможно параметр ORACLE_HOME задан неверно.

Ошибка 7303

Данная ошибка возвращается SQL сервером в случае, когда не удалось проинициализировать объект источника данных поставщика OLE DB, например, в нашем случае «OraOLEDB.Oracle». Причин, по которым она появляется много, поэтому нам необходимо смотреть ошибку, которую вернул сам провайдер.

Ошибки при инициализации провайдера OraOLEDB.Oracle

Описание ошибки Возможные причины, устранение
ORA-01017: invalid username/password; logon denied Неправильно указан логин и пароль к БД Oracle.
ORA-12154: TNS:could not resolve the connect identifier specified Файл tnsnames.ora отсутствует или в нем указан несуществующий SID и SERVICE_NAME. Также возможно Вы неправильно ввели его при создании связанного сервера в строке «Источник данных».
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor В файле tnsnames.ora неправильно введен SERVICE_NAME
ORA-12533: TNS:illegal ADDRESS parameters Некорректное название параметров в файле tnsnames.ora (например, ADDRESS).
ORA-12541: TNS:no listener Ошибка означает, что у Вас в файле tnsnames.ora неправильно указан PORT, на котором работает сервер Oracle. Необходимо узнать на каком порту работает сервер Oracle, и указать его в этом параметре.
ORA-12545: Connect failed because target host or object does not exist Данная ошибка возвращается тогда, когда в файле tnsnames.ora неправильно указан (или просто недоступен) сервер Oracle, т.е. параметр HOST. В данном случае необходимо: проверить данный параметр, проверить доступность сервера.

В случае если у Вас в каких-нибудь сообщениях выскакивает провайдер OLE DB «MSDAORA» (или любой другой провайдер или вообще он не указан), то это означает, что Вы неправильно выбрали провайдера при создании связанного сервера. MSDAORA – это провайдер от компании Microsoft. OraOLEDB.Oracle – от компании Oracle, как я говорил, рекомендовано использовать провайдер от Oracle.

Теперь давайте рассмотрим несколько ошибок, которые могут возникнуть уже после создания связанного сервера, т.е. связанный сервер создан, но при обращении к его объектам возникает ошибка.

Ошибка 7314

«Поставщик OLE DB «OraOLEDB.Oracle» для связанного сервера «Название сервера» не содержит таблицы «Название таблицы». Таблица либо не существует, либо текущий пользователь не имеет разрешения на доступ к ней.»

Здесь Вы, наверное, ошиблись в написании самого объекта, например, написали имя объекта в неправильном регистре. Oracle чувствителен к регистру, если к нему обращаться через ссылку связанного сервера, например

   
    SELECT * FROM ORACLEDB..ORACLEUSER.OracleTable

В этом случае если таблица OracleTable на сервере Oracle создана как ORACLETABLE, у Вас возникнет ошибка. Чтобы этого избежать, можно указать правильный регистр или использовать конструкцию OPENQUERY, например

   
    SELECT * FROM OPENQUERY(ORACLEDB, 'SELECT * FROM OracleTable')

Также не исключено что объекта действительно нет или у Вас отсутствуют необходимые разрешения.

Для того чтобы посмотреть, какие объекты и в каком регистре есть на сервере (т.е. узнать точное имя) можно использовать процедуру sp_tables_ex, например

   
    EXEC sp_tables_ex @table_server=«Имя связанного сервера», @table_schema='«Имя пользователя»'

Ошибка 7356

«Поставщик OLE DB «MSDAORA» для связанного сервера «Название сервера» предоставил несогласованные метаданные для столбца.»

Данную ошибку я встречал у поставщика MSDAORA, когда пытался обратиться к таблице, в которой нет данных, т.е. она пустая (нет строк). Эту ошибку можно избежать, если использовать конструкцию OPENQUERY.

Ошибка 7357

«Не удалось обработать объект «Название объекта». Поставщик OLE DB «OraOLEDB.Oracle» для связанного сервера «Название сервера» обнаружил, что у объекта либо нет ни одного столбца, либо текущий пользователь не имеет разрешения на доступ к объекту.»

В данном случае скорей всего Вы пытаетесь обратиться к таблице (или другому объекту) от имени пользователя, который не имеет разрешения на доступ к ней. Другими словами, у пользователя, которого Вы указывали при создании связанного сервера, нет необходимых прав.

Заметка! Если Вас интересует язык SQL, рекомендую почитать мою книгу «SQL код», которая ориентирована на изучение SQL как стандарта, после прочтения книги Вы сможете писать SQL запросы в любой системе управления базами данных.

На этом у меня все, пока!

Понравилась статья? Поделиться с друзьями:
Заметки IT специалиста
Комментарии: 4
  1. Baur

    добрый день, я установил х64 но сам Oracle оказался х32. Теперь как удалить х64?

    1. Админ (автор)

      Здравствуйте, у Вас какая система, на которой установлен MS SQL Server? если x64, то Oracle Provider for OLE DB также необходимо скачивать и устанавливать x64.
      Какие ошибки (проблемы) у Вас возникают при работе с уже установленной версией Oracle Provider for OLE DB?

  2. Игорь

    Добрый день! Установил драйвер на windows server 2012 r2, перезагрузил. Но в списке провайдеров не появился oraOLEDB.Oracle…

  3. Игорь

    После установки и перезагрузки не появился нужный провайдер.

Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:
Нажимая на кнопку «Отправить комментарий», я даю согласие на обработку персональных данных и принимаю политику конфиденциальности.