Как настроить связанный сервер с базой данных Oracle на SQL 2000/2005?

Я могу создать и выполнить пакет DTS, который копирует таблицы из удаленной базы данных Oracle на локальный SQL-сервер, но хочет настроить подключение к базе данных Oracle как связанный сервер.

В пакете DTS в настоящее время используется поставщик Microsoft OLE DB для Oracle со следующими свойствами:

Источник данных: SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.3.42)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=acc)));uid=*UserName*;pwd=*UserPassword*;
Пароль: UserPassword
Идентификатор пользователя: UserName
Разрешить сохранение пароля: true

Как мне настроить привязанный сервер к базе данных Oracle с использованием источника данных, определенного выше?

Ответ 1

Мне удалось установить связанный сервер с удаленной базой данных Oracle, которая оказалась многоэтапной:

Установите драйверы ODBC Oracle на SQL Server.
Создать системную DSN-базу данных Oracle на SQL Server.
Создайте связанный сервер на SQL-сервере с помощью System DSN.

Шаг 1: Установите драйверы ODBC Oracle на сервер

а. Загрузите необходимые пакеты Oracle Instant Client: Basic, ODBC и SQL * Plus (необязательно)

б. Разархивируйте пакеты в локальный каталог на сервере SQL, обычно C:\Oracle. Это должно привести к [каталогу], подобному C:\Oracle\instantclient_10_2, который будет значением [directory], указанным в остальной части этого ответа.

с. Создайте текстовый файл с именем tnsnames.ora в текущем клиенте [каталог], который содержит следующее:

OracleTnsName = 
(
  DESCRIPTION=
  (
    ADDRESS = (PROTOCOL=TCP)(HOST=10.1.3.42)(PORT=1521)
  )
  (
    CONNECT_DATA = (SERVICE_NAME=acc)
  )
)

Примечание. Фактические HOST, PORT и SERVICE_NAME будут меняться в зависимости от сервера Oracle, с которым вы устанавливаете соединение. Эта информация часто может быть найдена с использованием инструментов сетевого клиента Oracle под слушателями.

OracleTnsName может быть любым именем, которое вы хотите назначить источнику данных Oracle, и будет использоваться при настройке системного DSN. Вы также можете использовать синтаксис выше для определения нескольких имен TNS в том же файле tnsnames.ora, если это необходимо.

д. Добавьте [каталог] в переменную окружения PATH.

е. Создайте новую переменную системной среды с именем TNS_Admin, которая имеет значение [каталог]

ф. Выполните программу [directory]\odbc_install.exe, чтобы установить драйверы ODBC Oracle.

г. Рекомендуется перезагрузить SQL-сервер, но может и не понадобиться. Кроме того, вы можете предоставить разрешения безопасности для этого каталога для идентификаторов пользователей SQL-сервера и SQL-агента.

Шаг 2. Создание системного DNS, использующего драйвер ODBC Oracle

а. Откройте инструмент администратора источника данных ODBC. [Инструменты администрирования → Источники данных (ODBC)]

б. Выберите вкладку "Системный DSN" и затем нажмите кнопку "Добавить".

с. В списке драйверов выберите Oracle в instantclient {version}. (например, "Oracle в instantclient 10_2" ), а затем нажмите кнопку "Готово".

д. Укажите следующее:

Data Source Name: {Имя системного DSN}
Description: {оставить пустым/пустым}
TNS Service Name: должен быть указан OracleTnsName в файле tnsnames.ora, выберите его как значение.
Идентификатор пользователя: {Имя пользователя Oracle}

е. Выберите "Проверить соединение". Вам будет предложено предоставить {Oracle пароль пользователя}. Если все пройдет успешно, тест будет успешным.

Шаг 3. Создание связанного сервера в SQL с базой данных Oracle

Откройте окно запроса на сервере SQL и выполните следующее:

EXEC sp_addlinkedserver 
     @server        = '{Linked Server Name}'
    ,@srvproduct    = '{System DSN Name}'
    ,@provider      = 'MSDASQL'
    ,@datasrc       = '{System DSN Name}'

EXEC sp_addlinkedsrvlogin 
     @rmtsrvname    = '{Linked Server Name}'
    ,@useself       = 'False'
    ,@locallogin    = NULL
    ,@rmtuser       = '{Oracle User Name}'
    ,@rmtpassword   = '{Oracle User Password}'

Примечание. {Linked Server Name} может быть любым, что вы хотите использовать при ссылке на сервер Oracle, но {System DNS Name} должен соответствовать имени созданного ранее системного DSN.

{Oracle User Name} должен совпадать с идентификатором пользователя, используемым системным DSN, а {Oracle User Password} должен быть таким же, как вы использовали для успешной проверки соединения ODBC. См. KB 280106 для получения информации об устранении проблем, связанных с сервером Oracle.

Запрос связанного сервера Oracle

Вы можете использовать OPENQUERY для выполнения сквозных запросов на связанном сервере Oracle, но имейте в виду, что для очень больших наборов записей вы можете получить a ORA-01652, если вы указываете предложение ORDER BY в сквозном запросе. Перемещение предложения ORDER BY из сквозного запроса в оператор внешнего выбора решило эту проблему для меня.

Ответ 2

У меня была та же проблема. Я часами разговаривал с Microsoft, и у них не было решения. Ни один из этих настроек тайм-аута подключения не помог мне.

Чтобы решить эту проблему, я создал задание DTS, которое запускает proc, который обновляет время только в одной строке, в одном столбце, каждые две минуты. Затем я настраиваю репликацию между SQL Server и Oracle, планируя реплицировать изменение одной ячейки с SQL на Oracle каждые 3 минуты. Он поддерживает связь!