Предлагаю рассмотреть иногда очень полезную, и в некоторых случаях незаменимую возможность SQL сервера от Microsoft, а именно «Связанные серверы», на примере MS SQL Server 2014 Express. Мы с Вами узнаем, что это такое, как это можно использовать, и, конечно же, рассмотрим примеры создания этих самых связанных серверов.
И начать я предлагаю с обзора возможности «Связанные серверы» SQL сервера, а затем плавно перейти к созданию связанных серверов, причем мы научимся это делать двумя способами, через графический интерфейс Management Studio и с использованием языка Transact-SQL, т.е. используя системную процедуру, а также научимся обращаться к связанным серверам.
Актуальные статьи про связанные серверы 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.
На этом все! Надеюсь, что функционал связанных серверов Вам будет полезен. Удачи!
Добрый день. У меня не получается подключить ни эксель ни аксес