Подводные камни при использовании Linked Server

[AlanDenton]

Спасибо за статью. Когда читал чуть слезу не пустил, потому что сражался примерно с такими же проблемами :)

Вариант с sys.sp_executesql действительно очень хороший. НО! Он не всегда будет работать. Если в настройках линкед-сервера отключен RPC, то запрос будет падать по ошибке:

DECLARE @SP_CMD NVARCHAR(50) = N'[linked_server].[AdventureWorks2012].sys.sp_executesql'
DECLARE @SQL_CMD NVARCHAR(4000) = N'SELECT name FROM sys.databases'
EXEC @SP_CMD @SQL_CMD

Server '...' is not configured for RPC.

В большинстве случаев, это можно игнорировать, но в моем случае мы разрабатывали плагин для SSMS, который должен был подсказывать объекты линкед-сервера и работать при этом на любой конфигурации. Настройки менять не разрешалось. В результате множества тестов вот такой вариант оставили:

SELECT name
FROM [linked_server].[AdventureWorks2012].sys.databases

[akotelevets]

Когда читал чуть слезу не пустил, потому что сражался примерно с такими же проблемами :)
Я рад, что я не один такой… Т.к. в кругу моих коллег, не кто с такой проблемой не сталкивался.

Если в настройках линкед-сервера отключен RPC, то запрос будет падать по ошибке:
Да об этом можно было описать, но это относится к вопросы создания LinkedServer и его конфигурации

[AGhost]

SELECT name
FROM [linked_server].[AdventureWorks2012].sys.databases

— так это же самый первый, обычно используемый вариант, при котором у автора и были проблемы… или не так?

[akotelevets]

Структура запроса таже.

[alexhott]

Вы не дошли до самого эффективного метода
openqwery засунуть в sp_executesql
Тогда можно передать параметры и работать с таблицами содержащими xml поля

[akotelevets]

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

[npocmu]

Я я всегда считал что 4D адресация для линкед-серверов, это всего лишь возможности драйвера, а не СУБД. В частности она у меня работает с линкед-сервером DB2 (правда с драйвером OLE/DB от Микрософт). К сожалению, разработчики драйверов для СУБД не заморачиваются поддержкой этой фичи, и тогда, да — остается только OPENQUERY

[Vlacodlak]

в общем, использовать указание сервера и динамического запроса в openquery можно, но с одной небольшой уловкой:
1. Выполняете select * from operowset('SQLNCLI', 'Server=Self_servername\instancename;UID=username;PWD=password;', @dsql), где @dsql — динамический запрос с openquery (одно но — должно возвращать выборку, и если идет изменение данных, то надо настраивать MSDTC)
2. использовать @dsql совместно с sp_executesql, но если необходимо вернуть данные и сними еще что-то сделать (select и проч), то придется заворачивать ответ (таблицу) в строку и потом ее возвращать. Для определенных select (когда колонки известны) — конструируем определенный xml ответ (select id as "@id", data as "@data" from openquery(...) for xml path('element'), root('Result')), либо если не известная сигнатура ответа то генерация в авто режиме (select * from openquery(...) for xml auto). После получения строки из sp_executesql ее можно развернуть обратно в табличку.

[Vlacodlak]

Так же отмечу, что встречались случаи, когда необходимо было хранить информацию о настройках подключаемых БД в таблицах. Клиент мог завести кучу подключений к разным БД в систему, тогда openrquery не подходит (слишком много link server появляется). Из ситуации как раз и спасал динамический @dsql = select * openrowset(provider, строка с подключением, скрипт), который в дальнейшем выполнялся через sp_executesql + xml обертка.

[alexhott]

Вроде как openquery напрямую поддерживает insert и update

UPDATE OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles WHERE id = 101')
SET name = 'ADifferentName';

INSERT OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles')
VALUES ('NewTitle');

DELETE OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles WHERE name = ''NewTitle''');

[akotelevets]

Да, так и есть. Но:

1. Параметры — задаются на этапе компиляции запроса;
2. Имя сервера указываться на этапе компиляции запроса.
   Все это можно обойти используя бубен и это оправдано, когда у вас LinkedServer не MsSql Server.