Связанные серверы в Microsoft SQL Server 2014 – создание и примеры использования

Предлагаю рассмотреть иногда очень полезную, и в некоторых случаях незаменимую возможность SQL сервера от Microsoft, а именно «Связанные серверы», на примере MS SQL Server 2014 Express. Мы с Вами узнаем, что это такое, как это можно использовать, и, конечно же, рассмотрим примеры создания этих самых связанных серверов.

И начать я предлагаю с обзора возможности «Связанные серверы» SQL сервера, а затем плавно перейти к созданию связанных серверов, причем мы научимся это делать двумя способами, через графический интерфейс Management Studio и с использованием языка Transact-SQL, т.е. используя системную процедуру, а также научимся обращаться к связанным серверам.

Актуальные статьи про связанные серверы SQL Server:

• Что такое связанные серверы в Microsoft SQL Server
• Как создать связанный сервер в Microsoft SQL Server
• Как удалить связанный сервер в Microsoft SQL Server

Что такое Связанные серверы в MS SQL?

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

Мы с Вами уже немного затрагивали тему подключения к другим серверам, используя инструкции Transact-SQL, в материале про межбазовые запросы, но функционал связанных серверов позволяет не писать сложные, непонятные sql инструкции, так как мы настраиваем один раз это подключение, а потом в запросе в качестве источника указываем наше подключение. Как Вы понимаете — это очень полезно, например, если Вы часто обращаетесь к другим источникам данных, и при этом писать такие конструкции как opendatasource Вы не хотите.

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

Используя функционал связанных серверов можно подключаться к таким источникам как: Microsoft SQL Server, Access (mdb), Excel (xls), DBF, PostgreSQL, Oracle, текстовым файлам (txt, csv) и многим другим. В качестве поставщика может выступать, например такие компоненты как: Microsoft Jet 4.0 OLE DB Provider или драйвера ODBC.

Как я уже сказал создать связанный сервер можно двумя способами, первый — это используя функционал Management Studio, а второй, на Transact-SQL с помощью процедуры sp_addlinkedserver.

Исходные данные и задача

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

Примечание! Примеры мы будем реализовывать, используя версию СУБД MS SQL Server 2014 Express и соответственно такую же версию среды Management Studio.

Пример файла Access

Название файла Test_File_Access.mdb, название таблицы test_table.

Пример файла Excel

Название файла, как видите Test_File_Excel.xls, название листа list. Сами данные пусть будут одинаковые.

Создание связанного сервера с использованием Management Studio

Пример с Access

Открываем Management Studio и в обозревателе объектов ищем пункт «Связанные серверы», затем щелкаем правой кнопкой мыши по данному пункту и выбираем «Создать связанный сервер»

После чего откроется окно «Создание связанного сервера», в котором заполняем необходимые поля.

Где,

• Связанный сервер – т.е. название нашего тестового сервера;
• Поставщик – выбираем из выпадающего списка, в нашем случае «Microsoft Jet 4.0 OLE DB Provider»;
• Название продукта – т.е. OLE DB;
• Источник данных – это путь к нашей базе Access, т.е. путь к файлу mdb;
• Строка поставщика – это полное наименование поставщика, в случае с mdb можно ничего не указывать, так как по умолчанию здесь подразумевается как раз Access.

Все жмем «ОК».

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

Пример с Excel

Также открываем окно создание сервера и заполняем поля

Здесь практически все то же самое только меняем, конечно же, название нашего связанного сервера, источник данных, т.е. путь, указываем к файлу Excel и в строке поставщика пишем «Excel 8.0» — это для Excel 2003. Жмем «ОК». И если все хорошо, то у нас также появится новый связанный сервер, где у нас в виде таблиц будут наши листы файла Excel.

Создание связанного сервера с использованием системной процедуры sp_addlinkedserver

Чуть выше мы создали связанные сервера с использованием Management Studio, а если бы мы это делали на Transact-SQL, то использовали процедуры sp_addlinkedserver и sp_addlinkedsrvlogin, а SQL инструкции выглядели следующим образом, но результат был бы одинаковый:

Для создания связанного сервера с Access

  
  --создание связанного сервера
  EXEC dbo.sp_addlinkedserver 'TEST_ACCESS', 
                              'OLE DB', 
                              'Microsoft.Jet.OLEDB.4.0', 
                              'C:\Test\Test_File_Access.mdb'
  --Настройки безопасности (Авторизации)
  EXEC dbo.sp_addlinkedsrvlogin 'TEST_ACCESS',
                                 'False',
                                  NULL,
                                  NULL,
                                  NULL

Я думаю понятно, что какой параметр означает.

Для создания связанного сервера с Excel

В качестве примера предлагаю использовать указание параметров вот таким образом

  
  --создание связанного сервера
  EXEC dbo.sp_addlinkedserver @server = N'TEST_EXCEL', 
                              @srvproduct=N'OLE DB', 
                              @provider=N'Microsoft.Jet.OLEDB.4.0', 
                              @datasrc=N'C:\Test\Test_File_Excel.xls', 
                              @provstr=N'Excel 8.0'
  --Настройки безопасности (Авторизации)
  EXEC dbo.sp_addlinkedsrvlogin @rmtsrvname=N'TEST_EXCEL',
                                @useself=N'False',
                                @locallogin=NULL,
                                @rmtuser=NULL,
                                @rmtpassword=NULL

Для удаления связанного сервера

  
  EXEC sp_dropserver 'TEST_EXCEL', 'droplogins'

где, как Вы понимаете, sp_dropserver системная процедура для удаления подключенных серверов, первым параметром идет название нашего связанного сервера, а вторым мы указываем, что все связанные имена входа удаленных и связанных серверов будут удалены.

Для просмотра подключенных серверов

  
  SELECT * FROM sys.servers
  SELECT * FROM sys.linked_logins

где, sys.servers системное представление, которое показывает все подключенные сервера, а sys.linked_logins системное представление, показывающее имена входа удаленных и связанных серверов.

Пример использования связанного сервера

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

  
  SELECT 'EXCEL' AS Source, * FROM TEST_EXCEL...list$
        UNION ALL
  SELECT 'ACCESS' AS Source,* FROM TEST_ACCESS...test_table

Можно кстати использовать и вставку данных в эти источники, например, давайте вставим данные в файл Excel и сразу проверим их

Как видите, строка вставилась, и если Вы откроете сам файл, то увидите, что действительно добавилась одна строка.

Заметка! Если Вас интересует SQL и T-SQL, рекомендую посмотреть мои видеокурсы по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать с использованием языка T-SQL в Microsoft SQL Server.

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