Работа с удалёнными источниками данных (OPENQUERY, OPENROWSET, EXEC AT)

Работа с удалёнными источниками данных (OPENQUERY, OPENROWSET, EXEC AT)

Перевод и дополнение: Dynamic SQL execution on remote SQL Server using EXEC AT

До 2005 SQL Server, для работы с удалёнными источниками данных (другими СУБД или SQL Server), мы могли воспользоваться только OPENQUERY и OPENROWSET, которые имеют ряд ограничений. Но начиная с SQL Server 2005 появился ещё один способ EXEC AT. EXEC AT выполняет запросы к заранее созданному Linked Server без недостатков OPENQUERY и OPENROWSET.

Сначала я рассказу немного о OPENQUERY и OPENROWSET и потом перейду к «EXEC AT».

OPENQUERY

Табличная функция, которая использует Linked Server через OLE DB. OPENQUERY может использоваться рядом с FROM в запросе, словно мы обращаемся к локальной таблице. OPENQUERY может работать с INSERT, UPDATE или DELETE. Проблемы возникают когда результирующий набор должен вернуть несколько результатов, OPENQUERY получит только один из них. Так же он не позволяет использовать переменные для своих аргументов, другими словами они должны быть статичными (хотя есть некоторые уловки).

OPENROWSET

Отличительной особенностью является то, что OPENROWSET не использует Linked Server и может вернуть множественные результаты вывода.

EXEC AT

В SQL Server 2005 было представлено улучшение для работы с удалёнными источниками данных — EXEC, который позволяет выполнять динамический SQL по верх Linked Server. Одним из параметров вызова EXEC является EXEC AT, который призван обойти ограничения OPENQUERY и OPENROWSET. EXEC AT — это динамический SQL выполняемый по верх Linked Server, который может вернуть любое количество результирующих наборов.

Примеры:

Рассмотрим как можно использовать EXEC AT:

  • Скрипт 1: Создаёт Linked Server
  • Скрипт 2: Выполняет простой SELECT к созданному Linked Server и возвращает один результирующий набор
  • Скрипт 3: Выполняет два SELECT и возвращает два результирующих набора
  • Скрипт 4: SELECT с двумя аргументами в динамическом SQL
  • Скрипт 5: Аналогично скрипту 4, но с переменными
  • Скрипт 6: Выполнит создание объекта на удалённом сервере
  • Скрипт 7: Удалит все объекты, созданные нашей сессией

Обратите внимание

  • Вы можете получить ошибку «Сервер myserver» не настроен для RPC, чтобы её исправить, вы должны включить RPC:
  • До выполнения EXEC AT с текстовыми переменными, вы должны их валидировать (проверять). Никогда не исполняйте команды без этой проверки, так как оставляете дыру в безопасности (SQL Injection)
  • Если вы используете именованный экземпляр и хотите зарегистрировать сервер без \, то вам поможет следующий синтаксис:

Дополнение:

На форуме sql.ru нашёл интересное сравнение механизмов OPENQUERY и OPENROWSET:

OPENQUERY требует предварительно добавленный и сконфигурированный Linked Server.
OPENDATASOURCE/OPENROWSET используют явно прописанную строку соединения.

OPENQUERY требует текст запроса к удаленному серверу
OPENDATASOURCE требует имя объекта на удаленном сервере
OPENROWSET работает как с первым та и со вторым.

OPENQUERY всегда 100% передает запрос на выполнение удаленному серверу (pass-through query).
Для OPENDATASOURCE/OPENROWSET оптимизатор в зависимости от запроса может выбрать и передачу данных с удаленного сервера и выполнение запроса локально. Но такая возможность во многом зависит от удаленного сервера.
(для многих провайдеров он так и делает. Собственно я видел что эти функции могут выполнять запрос удаленно только для MSSQL серверов)

Для OPENQUERY не нужно соблюдать fourpart name convention для доступа к объектам.
Для OPENDATASOURCE (OPENROWSET при обращении к объекту) обязательно соблюдать.
Т.е. если провайдер не поддерживает такой способ обращения к объектам удаленного сервера, то использовать OPENDATASOURCE (OPENROWSET) невозможно.

Запись опубликована в рубрике Обучение с метками , . Добавьте в закладки постоянную ссылку.

Один комментарий на «Работа с удалёнными источниками данных (OPENQUERY, OPENROWSET, EXEC AT)»

  1. Алексей Перменов говорит:

    «по верх» пишется слитно

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

Войти с помощью: