openquery ms sql с параметрами

Работа с удалёнными источниками данных (OPENQUERY, OPENROWSET, 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:

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

Дополнение:

На форуме 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 (Transact-SQL)

Выполняет указанный передаваемый запрос к указанному связанному серверу. Этот сервер является источником данных OLE DB. Из предложения FROM запроса можно ссылаться на функцию OPENQUERY как на имя таблицы. На функцию OPENQUERY можно также ссылаться как на целевую таблицу инструкции INSERT, UPDATE или DELETE. Это зависит от возможностей поставщика OLE DB. Запрос может возвратить несколько результирующих наборов, но функция OPENQUERY возвращает только первый.

Синтаксические обозначения в Transact-SQL

Синтаксис

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Аргументы

linked_server
Идентификатор, представляющий имя связанного сервера.

‘ запрос ‘
Строка запроса, выполненного в связанном сервере. Максимальная длина строки 8 КБ.

Комментарии

В качестве аргументов в функции OPENQUERY нельзя использовать переменные.

Функцию OPENQUERY нельзя использовать для выполнения расширенных хранимых процедур на связанном сервере. Однако расширенную хранимую процедуру можно выполнить на связанном сервере с помощью четырехкомпонентного имени. Пример:

Любой вызов функции OPENDATASOURCE, OPENQUERY или OPENROWSET в предложении FROM вычисляется отдельно и независимо от любого вызова этих функций, используемого как назначение при обновлении, даже если в двух таких вызовах будут заданы идентичные аргументы. В частности, условия фильтра или соединения, применяемые к результатам одного из таких вызовов, никак не влияют на результаты другого.

Разрешения

Функцию OPENQUERY может выполнить любой пользователь. Разрешения, используемые для подключения к удаленному серверу, извлекаются из настроек, определенных для связанного сервера.

Примеры

A. Выполнение передаваемого запроса UPDATE

В следующем примере используется передаваемый запрос UPDATE к связанному серверу, созданному в примере А.

Б. Выполнение передаваемого запроса INSERT

В следующем примере используется передаваемый запрос INSERT к связанному серверу, созданному в примере А.

В. Выполнение передаваемого запроса DELETE

В следующем примере используется передаваемый запрос DELETE для удаления строки, созданной в примере В.

Г. Выполнение передаваемого запроса SELECT

В приведенном ниже примере используется передаваемый запрос SELECT для выбора строки, вставленной в примере В.

Источник

включая параметры в OPENQUERY

Как я могу использовать параметр внутри sql openquery, например:

В документации OPENQUERY говорится, что:

OPENQUERY не принимает переменные за свои аргументы.

Смотрите эту статью для обходного пути.

Обновление:

Как и предполагалось, я включаю рекомендации из статьи ниже.

Передать основные значения

Если базовая инструкция Transact-SQL известна, но вам необходимо передать одно или несколько конкретных значений, используйте код, подобный следующему примеру:

Передайте весь запрос

Когда вам нужно передать весь запрос Transact-SQL или имя связанного сервера (или оба), используйте код, подобный следующему примеру:

Используйте хранимую процедуру Sp_executesql

Чтобы избежать многослойных кавычек, используйте код, подобный следующему примеру:

Вы можете выполнить строку с OPENQUERY, как только вы ее создадите. Если вы идете этим путем, подумайте о безопасности и постарайтесь не объединять введенный пользователем текст в ваш SQL!

OPENQUERY не принимает переменные для своих аргументов

По сути, это означает, что вы не можете выполнить динамический запрос. Чтобы добиться того, что пытается сделать ваш образец, попробуйте это:

На самом деле, мы нашли способ сделать это:

Это назначит результат выполнения OpenQuery в переменной @Output.

Мы тестировали процедуру Store в MSSQL 2012, но должны работать с MSSQL 2008+.

Источник

Макрокоманда OpenQuery

Область применения: Access 2013, Office 2013

Вы можете использовать действие OpenQuery для открытия выбора или перекрестного запроса в представлении Datasheet, Представлении дизайна или Предварительном просмотре печати. Это действие выполняет запрос действий. Вы также можете выбрать режим ввода данных для запроса.

Параметр

Действие OpenQuery имеет следующие аргументы.

Название запроса

Имя открываемого запроса. Поле Имя запроса в разделе Аргументы действий в области Macro Builder отображает все запросы в текущей базе данных. Это необходимый аргумент. Если вы запустите макрос, содержащий действие OpenQuery в базе данных библиотеки, Microsoft Access сначала ищет запрос с этим именем в базе данных библиотеки, а затем в текущей базе данных.

View

Представление, в котором откроется запрос. Щелкните таблицуданных, дизайн, предварительныйпросмотр печати, pivotTableили сводная диаграмма в поле Просмотр. По умолчанию — таблица данных.

Режим данных

Режим ввода данных для запроса. Это касается только запросов, открытых в представлении Datasheet. Щелкните Добавить (пользователь может добавлять новые записи, но не может изменять существующие записи), изменить (пользователь может изменять существующие записи и добавлять новые записи) или Только для чтения (пользователь может просматривать только записи). По умолчанию изменить.

Примечания

Если вы используете таблицу данных для аргумента Просмотр, Access отображает набор результатов, если запрос является выбором, перекрестием, союзом или сквозным запросом, свойство ReturnsRecords которого задалось да; и выполняет запрос, если это действие, определение данных или сквозной запрос, свойство ReturnsRecords которого задано No.

Действие OpenQuery аналогично двойному нажатию запроса в области навигации или правой щелкнуть запрос в области навигации и выбрать представление. С помощью этого действия можно выбрать дополнительные параметры.

Чтобы выполнить действие OpenQuery в Visual Basic для приложений (VBA), используйте метод OpenQuery объекта DoCmd.

Источник

OPENQUERY (Transact-SQL)

Executes the specified pass-through query on the specified linked server. This server is an OLE DB data source. OPENQUERY can be referenced in the FROM clause of a query as if it were a table name. OPENQUERY can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement. This is subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENQUERY returns only the first one.

Transact-SQL Syntax Conventions

Syntax

To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

Arguments

linked_server
Is an identifier representing the name of the linked server.

‘ query ‘
Is the query string executed in the linked server. The maximum length of the string is 8 KB.

Remarks

OPENQUERY does not accept variables for its arguments.

OPENQUERY cannot be used to execute extended stored procedures on a linked server. However, an extended stored procedure can be executed on a linked server by using a four-part name. For example:

Any call to OPENDATASOURCE, OPENQUERY, or OPENROWSET in the FROM clause is evaluated separately and independently from any call to these functions used as the target of the update, even if identical arguments are supplied to the two calls. In particular, filter or join conditions applied on the result of one of those calls have no effect on the results of the other.

Permissions

Any user can execute OPENQUERY. The permissions that are used to connect to the remote server are obtained from the settings defined for the linked server.

Examples

A. Executing an UPDATE pass-through query

The following example uses a pass-through UPDATE query against the linked server created in example A.

B. Executing an INSERT pass-through query

The following example uses a pass-through INSERT query against the linked server created in example A.

C. Executing a DELETE pass-through query

The following example uses a pass-through DELETE query to delete the row inserted in example C.

D. Executing a SELECT pass-through query

The following example uses a pass-through SELECT query to select the row inserted in example C.

Источник