Self-Learning – видеокурсы и книги по IT

Приветствую Вас на сайте Self-Learning.ru! Сегодня я расскажу Вам о том, как можно создать связанный сервер в Microsoft SQL Server, а также каким образом мы можем обращаться к этому серверу.

Как создать связанный сервер (Linked Server) в Microsoft SQL Server

Итак, в прошлом материале мы выяснили, что связанный сервер (Linked Server) – это объект на SQL Server, который хранит подключение к внешнему источнику данных. И к данному объекту мы можем обращаться и выполнять распределённые запросы к разнородным источникам данных, которые расположены за пределами SQL Server.

Иными словами, связанный сервер в Microsoft SQL Server обладает примерно такой же функциональностью, что и конструкции OPENDATASOURCE и OPENROWSET, но в случае с Linked Server нам не нужно непосредственно в запросе указывать строку подключения к источнику данных.

Создание связанного сервера в Microsoft SQL Server

Создать связанный сервер в Microsoft SQL Server можно следующим образом:

  • Используя инструкции T-SQL;
  • Используя графический интерфейс среды SQL Server Management Studio.

Сегодня мы рассмотрим оба способа.

 Заметка! Для комплексного и профессионального изучения языка SQL и T-SQL рекомендую посмотреть мои видеокурсы – https://self-learning.ru/courses/t-sql

Исходные данные для примеров

Давайте представим, что у нас есть файл Excel, к которому нам нужно периодически обращаться в своих запросах, поэтому мы решили создать связанный сервер для удобства.

Файл Excel мы сохранили на диск D и он содержит следующие данные.

Скриншот 1

Подготовка к созданию связанного сервера

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

В нашем случае мы будем использовать провайдер Microsoft.ACE.OLEDB.12.0 x64.

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

Чтобы проверить, есть ли у нас этот провайдер, мы можем запустить следующую процедуру или в обозревателе объектов открыть контейнер «Объекты сервера -> Связанные серверы -> Поставщики» и посмотреть там.


EXEC sp_enum_oledb_providers;

Скриншот 2

В случае если у Вас в списке нет нужного провайдера, Вам необходимо скачать его с официального сайта Microsoft и установить.

https://www.microsoft.com/en-us/download/details.aspx?id=13255

Создание связанного сервера на T-SQL

Для создания и управления связанными серверами в Microsoft SQL Server существуют специальные хранимые процедуры:

  • sp_addlinkedserver – процедура создания связанного сервера;
  • sp_addlinkedsrvlogin – процедура настройки безопасности связанного сервера.

Таким образом, чтобы создать связанный сервер, нам необходимо выполнить следующие инструкции.

--Создание связанного сервера
EXEC sp_addlinkedserver @server = 'TEST_EXCEL',
                        @srvproduct = 'Excel',
                        @provider = 'Microsoft.ACE.OLEDB.12.0',
                        @datasrc = 'D:\TestExcel.xlsx',
                        @provstr = 'Excel 12.0;IMEX=1;HDR=YES;';

--Настройки безопасности (авторизации)
EXEC sp_addlinkedsrvlogin @rmtsrvname='TEST_EXCEL',
                          @useself= 'False',
                          @locallogin=NULL,
                          @rmtuser=NULL,
                          @rmtpassword=NULL;

В результате выполнения данных инструкций будет создан связанный сервер, источником которого будет выступать файл Excel, а также будут выполнены определённые настройки для авторизации на данном сервере.

В обозревателе объектов отобразится данный сервер.

Скриншот 3

Описание параметров процедуры sp_addlinkedserver

  • @server – название связанного сервера;
  • @srvproduct – название продукта;
  • @provider – провайдер (поставщик);
  • @datasrc – источник данных;
  • @provstr – строка поставщика для подключения.

Описание параметров процедуры sp_addlinkedsrvlogin

  • @rmtsrvname – название связанного сервера;
  • @useself – указывает, как будет происходить авторизация, с указанием логина и пароля, либо с использованием сопоставления с контекстом безопасности;
  • @locallogin – имя входа на локальный сервер;
  • @rmtuser – удаленное имя входа, используемое для подключения к связанному серверу;
  • @rmtpassword – пароль для удаленного имени входа.

Заметка! Транзакции в T-SQL – основы для новичков с примерами.

Создание связанного сервера с помощью SQL Server Management Studio

Все то же самое, что мы сделали чуть выше с помощью инструкций T-SQL, мы можем выполнить и в графическом интерфейсе среды SQL Server Management Studio.

Для этого нажмите на контейнер «Связанные серверы» правой кнопкой мыши и выберите «Создать связанный сервер».

Скриншот 4

Затем в открывшемся окне внесите соответствующие данные для подключения (данные соответствуют параметрам процедуры sp_addlinkedserver).

Скриншот 5

Чтобы сделать точно такую же авторизацию, которую мы сделали в примере с использованием процедуры sp_addlinkedsrvlogin, необходимо перейти на вкладку «Безопасность» и выбрать пункт «Устанавливать с использованием текущего контекста безопасности имени для входа».

Скриншот 6

После этого нажать «ОК», и точно такой же связанный сервер с файлом Excel будет создан.

Использование связанного сервера в Microsoft SQL Server

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

При этом обратиться к связанному серверу мы можем с помощью двух способов:

  • Используя функцию OPENQUERY (рекомендованный способ);
  • Используя полное имя объекта.

Заметка! Представление (VIEW) в T-SQL – описание и примеры использования.

Обращение к связанному серверу с помощью OPENQUERY

OPENQUERY – эта функция, с помощью которой можно обратиться к связанному серверу и выполнить указанный SQL запрос.

На эту функцию можно даже ссылаться в инструкциях по модификации данных, т.е. мы можем изменять данные на связанном сервере.

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

Для примера давайте обратимся к связанному серверу и получим данные, которые хранятся в нашем тестовом файле Excel.

SELECT * FROM OPENQUERY (TEST_EXCEL, 'SELECT * FROM [Лист1$]');

Скриншот 7

Обращение к связанному серверу с помощью указания полного имени объекта

Как было отмечено, обратиться к связанному серверу мы можем не только с помощью функции OPENQUERY, но и путем простого обращения к нему как к объекту, указав полное имя источника данных, в нашем случае это таблица Excel. Ведь связанный сервер – это объект на сервере, поэтому, соответственно, мы можем к нему обращаться. Это делается следующим образом.

SELECT * FROM TEST_EXCEL...[Лист1$];

Скриншот 8

Как видим, результат у нас точно такой же.

Совет эксперта
Виталий Трунин
Основатель проекта Self-Learning.ru. Программист T-SQL.
Задать вопрос
Для комплексного изучения языка T-SQL, рекомендую пройти онлайн-курсы по T-SQL, на которых используется последовательная методика обучения и рассматриваются все конструкции языка T-SQL.

Как создать связанный сервер – видео-инструкция

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

Теги: # #
Понравилась статья? Поделитесь с друзьями:
Share on VK
VK
Share on Facebook
Facebook
Tweet about this on Twitter
Twitter
Share on Tumblr
Tumblr
Share on Reddit
Reddit
Email this to someone
email

Авторские онлайн-курсы по T-SQL

Оставьте комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Пролистать наверх