Работа с данными из удаленных источников

Чтение данных из удаленного источника с использованием связанных серверов

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

В следующем примере обе функции - и OPENROWSET, и OPENDATASOURCE - используются для извлечения результатов из одного удаленного источника данных. Этот пример находится в папке SqlScript Examples под именем OPENROWSETandOPENDATASOURCEUsedTogether.sql.

SELECT Orders.CustomerID, Orders.Date, Orders.Amount
FROM
OPENROWSET( "SQLOLEDB", "Server=Sales; Trusted_Connection=yes;",
             SalesDB.Sales.Orders) As Orders
INNER JOIN
     OPENDATASOURCE("SQLOLEDB", "Server=Sales;
                   Trusted_Connection=yes;').SalesDB.Sales.OrderDetails
     ON Orders.OrderID = OrderDetails.OrderID
WHERE Sales.Quarter = 3

Функции OPENROWSET и OPENDATASOURCE требуют указания параметров конфигурации соединения при каждом соединении, даже если речь идет о соединении с одним сервером.

Использование связанных серверов для установления соединения с любым источником данных

Вместо того, чтобы повторно устанавливать одни и те же настройки соединения при каждом применении функций OPENROWSET или OPENDATASOURCE, SQL Server позволяет сохранить эти параметры и назначить им идентификатор, чтоб их можно было повторно использовать, не вводя параметры конфигурации каждый раз заново; эта технология получила название "связанный сервер". Связанный сервер является, по сути, виртуальным сервером.

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

Важно. Использование связанных серверов позволяет использовать функции, не доступные при использовании функций OPENROWSET или OPENDATASOURCE. Например, можно:

• Управлять статической безопасностью
• Сопоставлять имена входа в систему
• Запрашивать информацию каталога
• Настраивать различные параметры соединения, такие, как время ожидания соединения по умолчанию.

Настройка связанного сервера при помощи T-SQL

Чтобы сконфигурировать связанный сервер с помощью кода T-SQL, необходимо использовать хранимую процедуру sp_addlinkedserver. Хранимая процедура sp_addlinkedserver для настройки конфигурации удаленных источников данных принимает несколько параметров.

В следующем примере демонстрируется готовый код T-SQL, необходимый для добавления удаленного сервера SQL Server в качестве связанного сервера. Код в этом разделе можно найти в файлах примеров в папке SqlScripts под именем LinkedServer.sql.

EXEC sp_addlinkedserver @server = "Sales",
@srvproduct='SQL Server' GO

Важно. Предыдущие версии SQL Server предоставляли хранимую процедуру sp_addserver, которая использовалась для добавления ссылки на удаленный сервер. Эта хранимая процедура доступна и в SQL Server 2005, но она предназначена только для обеспечения обратной совместимости. Поддержка хранимой процедуры sp_addserver будет исключена из следующих версий SQL Server. В дальнейшем следует использовать только хранимую процедуру sp_addlinkedserver.

Код для создания связанного сервера для файла Microsoft Excel.

- Связанный сервер для файла Excel 
EXEC sp_addlinkedserver
@server = "MyEmployees",
@srvproduct = "Jet 4.0",
@provider = "Microsoft.Jet.OLEDB.4.0",
@datasrc = "C:\Documents and Settings\User\My Documents\
                    Microsoft Press\Sql2005SBS_AppliedTechniques\
                    Chapter08\EmployeeList.xls', 
@provstr = "Excel 8.0" GO

Следующий код создает связанный сервер для базы данных Access.

- Связанный сервер для базы данных Access EXEC sp_addlinkedserver
@server = "PreSales",
@provider = "Microsoft.Jet.OLEDB.4.0",
@srvproduct = "OLEDB Provider for Jet",
@datasrc = "C:\Documents and Settings\User\My Documents\
                    Microsoft Press\Sql2005SBS_AppliedTechniques\
                    Chapter08\Northwind.mdb' GO

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

- Связанный сервер для базы данных Oracle 
EXEC sp_addlinkedserver
@server = "PostSales",
@srvproduct = "Oracle",
@provider = "msdaora",
@datasrc = "PostSalesDB" GO

Проверяем, какие связанные сервера определены в данный момент

1. Откройте SQL Server Management Studio и установите соединение с экземпляром SQL Server.
2. Запросите значения из системного представления sys.servers с фильтрацией по столбцу is_linked, как показано в следующем примере.

   SELECT * FROM sys.servers WHERE is_linked = 1
3. Изучите результаты, возвращенные системным представлением sys.servers. Оно возвращает строку для каждого связанного сервера, определенного в текущем экземпляре SQL Sever. Другие столбцы показывают текущую конфигурацию для каждого из связанных серверов. На следующем рисунке показан фрагмент результата этого запроса.
   

   
   
   Важно. При регистрации связанных серверов SQL Server не выполняет проверки корректности указанных параметров или существования имени сервера. Хранимая процедура sp_addlinkedserver всегда выполняется успешно. В случае, если вы указали неправильное значение параметра, об этом можно узнать только в процессе выполнения.

Удаление сконфигурированных связанных серверов

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

EXEC sp_dropserver "MyEmployees" GO

Хранимая процедура sp_dropserver получает один параметр, идентификатор связанного сервера, который нужно удалить.

Настраиваем конфигурацию связанного сервера через интерфейс SQL Server Management Studio

Конфигурацию связанных серверов можно настраивать и через SQL Server Management Studio.

1. Запустите SQL Server Management Studio и установите соединение с сервером, который нужно настроить.
2. Откройте Object Explorer (Обозреватель объектов) через меню View (Вид), как показано ниже, или нажав клавишу F8. Обозреватель объектов позволяет без лишних сложностей найти все сервисы и их компоненты определенного экземпляра сервера SQL Server и управлять ими.
   

   
   
3. В дереве объектов разверните экземпляр сервера SQL Server, который нужно сконфигурировать, откройте папку Server Object (Объекты сервера), а затем папку Linked Servers (Связанные серверы). В папке Linked Servers (Связанные серверы) перечислены все сконфигурированные на данный момент связанные серверы. В папке Providers (Поставщики) перечислены все установленные поставщики OLE DB, которые доступны в SQL Server для создания связанных серверов.
   

   
   
4. Щелкните правой кнопкой на папке Linked Servers (Связанные серверы) и выберите из контекстного меню команду New Linked Server (Создать связанный сервер), чтобы запустить мастер для настройки конфигурации связанного сервера.
5. В окне New Link Server (Создание нового связанного сервера), показанном ниже, сконфигурируйте все параметры доступ к удаленному источнику данных (см. рис. вверху следующей страницы).
   

   
   увеличить изображение
   
6. Перейдите на страницу Security (Безопасность) в панели Select A Page (Выбор страницы) в левой части окна. В результате вы окажетесь в окне, показанном на следующем рисунке. Если удаленный источник данных требует проверки подлинности, необходимо сопоставить локальную учетную запись на локальном сервере локальной учетной записи на удаленном сервере. Если аутентифицируемое имя входа является учетной записью Windows, то при вызове удаленного источника данных SQL Server выполняет роль аутентифицированного пользователя. В противном случае, если аутентифицируемое имя входа в SQL Server является именем входа SQL Server, то при вызове удаленного источника данных SQL Server выполняет удаленный вызов с использованием учетной записи службы SQL Server.
   

   
   увеличить изображение
   
7. Перейдите на страницу Server Options (Параметры сервера) в панели Select A Page (Выбор страницы) в левой части окна. В результате вы окажетесь в окне, показанном на следующем рисунке. Параметры сервера позволяют настроить все детали соединения с удаленным источником данных. Подробное объяснение значения каждого из этих параметров можно найти в теме "Свойства связанного сервера (Страница Параметры сервера)" в Электронной документации SQL Server 2005/
   

   
   
8. После настройки параметров конфигурации связанного сервера нажмите кнопку ОК. Обратите внимание на то, в дереве объектов в панели Server Explorer (Обозреватель серверов) появился новый значок, как показано ниже.
   

   
   

   При добавлении нового связанного сервера с помощью T-SQL или через SQL Server Management Studio будут возвращены совершенно одинаковые результаты.