Когда в качестве запроса связанному серверу передается обычный SELECT без каких-либо условий, то проблем не возникает. Правда редко когда удается обойтись обычным селектом, особенно когда в таблице на другой стороне лежат милиионы записей, необходимость иметь на своей стороне такой объем информации отсутствует, а взаимодействие серверов сильно ограничено шириной канала в интернет.
Понятно, что в данном случае чистый SELECT – не вариант, нужно ограничивать запрос с помощью WHERE. Вот только механизм работы linked серверов устроен так, что связанный сервер сначала возвращает весь объем данных на сервер с которого пришел запрос, а уж потом сам MS SQL применяет к массиву данных условие WHERE.
Для того чтобы обойти это ограничение необходимо использовать OPENQUERY. Запрос, завернутый в эту функцию, связанный сервер обработает на своей стороне и вернет готовый результат.
Приятно и то, что результат работы OPENQUERY можно использовать в качестве исходной таблицы в INSERT, UPDATE и DELETE запросах, и, разумеется, в обычном SELECT’e.
В качестве аргументов функции передаются: имя связанного сервера и текст запроса.
В общем виде OPENQUERY можно применить следующим образом:
SELECT * FROM OPENQUERY(linked_server, 'SELECT remote_table.column_1, remote_table.column_2 FROM remote_db WHERE remote_table.column_N IN (''value_1'',''value_2'')')
В связи с тем, что сам текст SQL-запроса в OPENQUERY должен быть окружен апострофами, то, для того, чтобы параметры для поиска были правильно обработаны, их нужно заключать в двойные апострофы.
OPENQUERY и динамические SQL-запросы
MSDN честно предупреждает, что в качестве аргументов OPENQUERY нельзя применять параметры. Поэтому, если применять OPENQUERY так, как показано в примере выше, о динамическом SQL можно забыть.
Чтобы обойти это ограничение, приведенный выше запрос можно завернуть в апострофы, затолкать его в переменную, а потом подсунуть эту переменную функции EXEC.
Предположим, что в условии WHERE, я хочу применить некий набор значений, который можно получить селектом из таблицы, потом затолкать эти значения в OPENQUERY и выполнить сформированный запрос на связанном сервере, а полученный результат записать в таблицу.
DECLARE @values varchar(MAX) DECLARE @sql varchar (MAX) SET @values = (SELECT [Column1] + ',' FROM [DB].[Table1] GROUP BY [Column1] FOR XML PATH('')) SET @values = REPLACE(@values,',',''',''') SET @sql = 'SELECT remote_table.column_1, remote_table.column_2 FROM remote_db WHERE remote_table.column_N IN (''' + @values + ''')' SET @sql = N'INSERT INTO [Table2] SELECT * FROM OPENQUERY(linked_server, ''' + REPLACE(@sql, '''', '''''') + ''')' EXEC (@sql)
Обратить внимание следует на следующие моменты: во-первых, в параметр @values, имеющий тип varchar, нельзя передать несколько значение из таблицы, так как это строковый параметр. Поэтому приходится объединять все значения из таблицы в одну строку, разделяя их запятыми. Для этого вполне можно применить XML PATH.
Во-вторых, главное не косячить с апострофами. Поэтому для начала я обрабатываю строку @values, проходясь по ней REPLACE’ ом, а после того, как будет сформирован запрос к связанному серверу, прохожусь по нему REPLACE’ом еще раз, экранируя апострофы, чтобы гамотно завернуть всю конструкцию в OPENQUERY.
Конечный SQL-запрос хранится в параметре @sql, который и передается функции EXEC.
Ограничение на длину запроса
К сожалению, когда в качестве значений для условия WHERE используется SELECT из таблицы, всегда есть вероятность получить довольно большо количество данных. В то же время, максимальная длина параметра @sql не может превышать 8000 символов. Поэтому при неудачном стечении обстоятельств в функцию EXEC будет передан обрезанный SQL запрос, который вернет ошибку.
Я решил бороться с этим, отправляя на связанный сервер запрос, содержащий максимум 250 значений в условии WHERE за раз.
DECLARE @values varchar(MAX) DECLARE @sql varchar (MAX) DECLARE @counter numeric(6,2); SET @counter = (SELECT COUNT([Column1]) FROM [DB].[Table1] GROUP BY [Column1]) -- Так как количество значений, передаваемых за раз условию WHERE -- 250, то количество полученных рядов нужно разделить на размер пакета. -- Если рядов будет меньше 250, то достаточно одной итерации, поэтому -- счетчик устанавливается в значение 1 SET @counter = CEILING(@counter/250) WHILE @counter <> 0 BEGIN -- Так как в каждой итерации данные записываются в результирующую таблицу, то -- можно построить запрос для формирования DynamicTable таким образом, чтобы в нее -- возвращались только те значения, для которых еще не загружены данные. WITH DynamicTable AS (SELECT A.[Column1] FROM [DB].[Table1] AS A WHERE NOT EXISTS (SELECT B.[Column1] FROM [DB].[Table2] AS B WHERE B.[Column1] = A.[Column1]) GROUP BY [Column1]) SELECT @values = (SELECT TOP 250 [Column1] + ',' FROM DynamicTable FOR XML PATH('')) SET @values = REPLACE(@values,',',''',''') SET @counter = @counter - 1 SET @sql = 'SELECT remote_table.column_1, remote_table.column_2 FROM remote_db WHERE remote_table.column_N IN (''' + @values + ''')' SET @sql = N'INSERT INTO [Table2] SELECT * FROM OPENQUERY(linked_server, ''' + REPLACE(@sql, '''', '''''') + ''')' EXEC (@sql) END
Ссылки:
OPENQUERY на MSDN: http://msdn.microsoft.com/ru-ru/library/ms188427.aspx
Динамический SQL запросы (Английский): http://www.sommarskog.se/dynamic_sql.htm
Уведомление: Использование OPENQUERY в качестве источника данных для обновления локальной таблицы | BI Tech