Работа с удалёнными источниками данных (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: Удалит все объекты, созданные нашей сессией
  
  Transact-SQL

  --Скрипт 1 : Create a linked server EXEC sp_addlinkedserver 'FARAWAYSERVER', 'SQL Server' --Скрипт 2 : Execute a simple SELECT statement on the linked server EXEC ('SELECT TOP 10 * FROM AdventureWorksLT.SalesLT.Customer') AT [FARAWAYSERVER]; GO --Скрипт 3 : Executing multiple SELECT statements on linked server and getting multiple resultsets EXEC ('SELECT TOP 10 * FROM AdventureWorksLT.SalesLT.Customer; SELECT TOP 10 * FROM AdventureWorksLT.SalesLT.CustomerAddress;') AT [FARAWAYSERVER]; GO --Скрипт 4 : Execute a SELECT statement on linked serer and pass two arguments at dynamically EXEC ('SELECT TOP 10 * FROM AdventureWorksLT.SalesLT.Customer WHERE CustomerID = ? AND LastName = ?', 10, 'Garza') AT [FARAWAYSERVER]; GO --Скрипт 5 : Execute a SELECT statement on linked serer and pass two arguments at dynamically --by using variables DECLARE @CustomerID AS INT DECLARE @LastName AS VARCHAR(100) SET @CustomerID = 10 SET @LastName = 'Garza' EXEC ('SELECT TOP 10 * FROM AdventureWorksLT.SalesLT.Customer WHERE CustomerID = ? AND LastName = ?', @CustomerID, @LastName) AT [FARAWAYSERVER]; GO --Скрипт 6 : Execute a DDL statement on linked server EXEC ( 'USE TempDB IF OBJECT_ID(''dbo.Table1'') IS NOT NULL DROP TABLE dbo.Table1 CREATE TABLE dbo.Table1 ( Column1 INT )' ) AT [FARAWAYSERVER]; --Скрипт 7 : Once you are done with your testing, clean up created objects EXEC ( 'USE TempDB IF OBJECT_ID(''dbo.Table1'') IS NOT NULL DROP TABLE dbo.Table1' ) AT [FARAWAYSERVER]; EXEC sp_dropserver 'FARAWAYSERVER'

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44

  --Скрипт 1 : Create a linked server EXEC sp_addlinkedserver 'FARAWAYSERVER', 'SQL Server'   --Скрипт 2 : Execute a simple SELECT statement on the linked server EXEC ('SELECT TOP 10 * FROM AdventureWorksLT.SalesLT.Customer') AT [FARAWAYSERVER]; GO   --Скрипт 3 : Executing multiple SELECT statements on linked server and getting multiple resultsets EXEC ('SELECT TOP 10 * FROM AdventureWorksLT.SalesLT.Customer; SELECT TOP 10 * FROM AdventureWorksLT.SalesLT.CustomerAddress;') AT [FARAWAYSERVER]; GO   --Скрипт 4 : Execute a SELECT statement on linked serer and pass two arguments at dynamically EXEC ('SELECT TOP 10 * FROM AdventureWorksLT.SalesLT.Customer WHERE CustomerID = ? AND LastName = ?', 10, 'Garza') AT [FARAWAYSERVER]; GO   --Скрипт 5 : Execute a SELECT statement on linked serer and pass two arguments at dynamically --by using variables DECLARE @CustomerID AS INT DECLARE @LastName AS VARCHAR(100) SET @CustomerID = 10 SET @LastName = 'Garza' EXEC ('SELECT TOP 10 * FROM AdventureWorksLT.SalesLT.Customer WHERE CustomerID = ? AND LastName = ?', @CustomerID, @LastName) AT [FARAWAYSERVER]; GO   --Скрипт 6 : Execute a DDL statement on linked server EXEC ( 'USE TempDB IF OBJECT_ID(''dbo.Table1'') IS NOT NULL DROP TABLE dbo.Table1 CREATE TABLE dbo.Table1 ( Column1 INT )' ) AT [FARAWAYSERVER];   --Скрипт 7 : Once you are done with your testing, clean up created objects EXEC ( 'USE TempDB IF OBJECT_ID(''dbo.Table1'') IS NOT NULL DROP TABLE dbo.Table1' ) AT [FARAWAYSERVER]; EXEC sp_dropserver 'FARAWAYSERVER'

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

• Вы можете получить ошибку «Сервер myserver» не настроен для RPC, чтобы её исправить, вы должны включить RPC:
  
  Transact-SQL

  exec sp_serveroption @server='myserver', @optname='rpc', @optvalue='true' exec sp_serveroption @server='myserver', @optname='rpc out', @optvalue='true'

  1 2	exec sp_serveroption @server='myserver', @optname='rpc', @optvalue='true' exec sp_serveroption @server='myserver', @optname='rpc out', @optvalue='true'

• До выполнения EXEC AT с текстовыми переменными, вы должны их валидировать (проверять). Никогда не исполняйте команды без этой проверки, так как оставляете дыру в безопасности (SQL Injection)
• Если вы используете именованный экземпляр и хотите зарегистрировать сервер без \, то вам поможет следующий синтаксис:
  
  Transact-SQL

  EXEC sp_addlinkedserver @server='SQL2005', @srvproduct='', @provider='SQLNCLI', @datasrc='SERVER1\SQL2005'

  1	EXEC sp_addlinkedserver @server='SQL2005', @srvproduct='', @provider='SQLNCLI', @datasrc='SERVER1\SQL2005'

Дополнение:

На форуме 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) невозможно.