sp_addlinkedserver (Transact-SQL)

Применятся к: даSQL Server (все поддерживаемые версии) даУправляемый экземпляр SQL Azure

Создает связанный сервер. Связанные серверы позволяют выполнять распределенные разнородные запросы к источникам данных OLE DB. После создания связанного сервера с помощью sp_addlinkedserverраспределенных запросов можно выполнять на этом сервере. Если связанный сервер определен как экземпляр SQL Server, можно выполнить удаленные хранимые процедуры.

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

Синтаксис

sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ]   
     [ , [ @provider= ] 'provider_name' ]  
     [ , [ @datasrc= ] 'data_source' ]   
     [ , [ @location= ] 'location' ]   
     [ , [ @provstr= ] 'provider_string' ]   
     [ , [ @catalog= ] 'catalog' ]   

Аргументы

[ @server = ] 'server'

Имя создаваемого связанного сервера. Сервер аргументов — sysname без значения по умолчанию.

[ @srvproduct = ] 'product_name'

Введите название продукта для источника данных OLE DB, который добавляется в качестве связанного сервера. Значение product_namenvarchar(128), значение по умолчанию равно NULL. Если значение равно SQL Server, provider_name, data_source, расположение, provider_string и каталог не обязательно указывать.

[ @provider = ] 'provider_name'

Введите уникальный программный идентификатор (PROGID) поставщика OLE DB, соответствующий этому источнику данных. Provider_name должен быть уникальным для указанного поставщика OLE DB, установленного на текущем компьютере. Значение provider_namenvarchar(128), значение по умолчанию равно NULL; однако если provider_name опущен, используется SQLNCLI.

Примечание

При использовании SQLNCLI SQL Server будет использовать последнюю версию поставщика OLE DB для собственного клиента SQL Server. Предполагается, что поставщик OLE DB будет зарегистрирован в реестре с указанным идентификатором PROGID.

Важно!

Предыдущие поставщики Microsoft OLE DB для SQL Server (SQLOLEDB) и собственный клиент OLE DB для SQL Server (SQLNCLI) объявляются нерекомендуемыми для новых разработок. Вместо этого используйте новый драйвер Microsoft OLE DB для SQL Server (MSOLEDBSQL), который будет обновлен с самыми последними серверными компонентами.

[ @datasrc = ] 'data_source'

Имя источника данных, как оно интерпретируется поставщиком OLE DB. Значение data_sourcenvarchar(4000). data_source передается в качестве свойства DBPROP_INIT_DATASOURCE для инициализации поставщика OLE DB.

[ @location = ] 'location'

Введите местонахождение базы данных, понятное поставщику OLE DB. Расположение значения— nvarchar(4000) с значением по умолчанию NULL. Расположение аргумента передается как свойство DBPROP_INIT_LOCATION для инициализации поставщика OLE DB.

[ @provstr = ] 'provider_string'

Строка подключения для конкретного поставщика OLE DB, указывающая уникальный источник данных. Значение provider_stringnvarchar(4000) с значением по умолчанию NULL. Аргумент provstr передается в IDataInitialize или задается в качестве свойства DBPROP_INIT_PROVIDERSTRING для инициализации поставщика OLE DB.

При создании связанного сервера для поставщика SQL SERVER NATIVE CLIENT OLE DB экземпляр можно указать с помощью ключевого слова SERVER, чтобы SERVER=servername\\instancename указать конкретный экземпляр SQL Server. Имя сервера — это имя компьютера, на котором выполняется SQL Server, а имя экземпляра — имя конкретного экземпляра SQL Server, к которому будет подключен пользователь.

Примечание

Чтобы получить доступ к зеркальной базе данных, строка соединения должна содержать имя базы данных. Это имя необходимо, чтобы предоставить поставщику доступа к данным возможность пытаться отработать отказ. Базу данных можно указать в параметре @provstr или @catalog . Кроме того, строка соединения может содержать имя партнера по обеспечению отработки отказа.

[ @catalog = ] 'catalog'

Каталог, который должен использоваться при подключении к поставщику OLE DB. Каталог значений — sysname с значением по умолчанию NULL. Каталог аргументов передается в качестве свойства DBPROP_INIT_CATALOG для инициализации поставщика OLE DB. Если связанный сервер определен для экземпляра SQL Server, каталог ссылается на базу данных по умолчанию, с которой сопоставлен связанный сервер.

Значения кода возврата

0 (успешное завершение) или 1 (неуспешное завершение)

Результирующие наборы

Нет.

Remarks

В следующей таблице показаны способы настройки связанного сервера для источников данных, доступных через поставщик OLE DB. Связанный сервер может быть настроен несколькими способами для конкретного источника данных; для одного типа источника данных возможны несколько строк. В этой таблице также показаны sp_addlinkedserver значения параметров, используемые для настройки связанного сервера.

Удаленный источник данных OLE DB Поставщик OLE DB product_name provider_name data_source расположение provider_string catalog
SQL Server Поставщик OLE DB корпорации Майкрософт SQL Server Native Client SQL Server 1 (по умолчанию)
SQL Server Поставщик OLE DB корпорации Майкрософт SQL Server Native Client SQLNCLI Сетевое имя SQL Server (для экземпляра по умолчанию) Имя базы данных (необязательно)
SQL Server Поставщик OLE DB корпорации Майкрософт SQL Server Native Client SQLNCLI имя\ сервера имя экземпляра (для конкретного экземпляра) Имя базы данных (необязательно)
Oracle, версия 8 или более поздняя Поставщик Oracle для OLE DB Любой OraOLEDB.Oracle Псевдоним для базы данных Oracle
Access/Jet Поставщик OLE DB для Jet (Майкрософт) Любой Microsoft.Jet.OLEDB.4.0 Полный путь к файлу базы данных Jet
Источник данных ODBC Поставщик Microsoft OLE DB для ODBC Любой MSDASQL Системный DSN источника данных ODBC
Источник данных ODBC Поставщик Microsoft OLE DB для ODBC Любой MSDASQL Строка подключения ODBC
Файловая система Поставщик Microsoft OLE DB для службы индексирования Любой MSIDXS Имя каталога службы индексирования
Электронная таблица Microsoft Excel Поставщик OLE DB для Jet (Майкрософт) Любой Microsoft.Jet.OLEDB.4.0 Полный путь к файлу Excel Excel 5.0
База данных IBM DB2 Поставщик Microsoft OLE DB для DB2 Любой DB2OLEDB См. поставщик OLE DB для DB2 (Майкрософт) документацию. Имя каталога базы данных DB2

1 Этот способ настройки связанного сервера заставляет имя связанного сервера совпадать с сетевым именем удаленного экземпляра SQL Server. Используйте data_source для указания сервера.

2 "Любой" означает, что название продукта может быть любым.

Поставщик OLE DB корпорации Майкрософт SQL Server Native Client является поставщиком, который используется с SQL Server, если имя поставщика не указано или SQL Server указано в качестве имени продукта. Даже если указано имя предыдущего поставщика (SQLOLEDB), оно все равно будет изменено на SQLNCLI при сохранении в каталог.

Параметры data_source, расположения, provider_string и каталога определяют базу данных или базы данных, на которые указывает связанный сервер. Если значение одного из этих аргументов равно NULL, то соответствующее свойство инициализации поставщика OLE DB не установлено.

В кластеризованной среде при указании имен файлов для указания источников данных OLE DB используйте формат UNC или общие диски для указания расположения.

Хранимая процедура sp_addlinkedserver не может быть выполнена в пользовательской транзакции.

Важно!

Управляемый экземпляр SQL Azure в настоящее время поддерживает только SQL Server, База данных SQL и другие Управляемый экземпляр SQL в качестве удаленных источников данных.

Важно!

Когда связанный сервер создается с помощью, sp_addlinkedserverдля всех локальных имен входа добавляется самосоединяемое сопоставление по умолчанию. Для поставщиков, не являющихся SQL Server, SQL Server имена входа, прошедшие проверку подлинности, могут получить доступ к поставщику в учетной записи службы SQL Server. Администраторам нужно рассмотреть применение процедуры sp_droplinkedsrvlogin <linkedserver_name>, NULL для удаления глобального сопоставления.

Разрешения

Для инструкции sp_addlinkedserver требуется разрешение ALTER ANY LINKED SERVER . (Диалоговое окно "SQL Server Management Studio Новый связанный сервер" реализуется таким образом, чтобы требуется членство в предопределенной sysadmin роли сервера.)

Примеры

A. Использование поставщика OLE DB корпорации Майкрософт SQL Server

В следующем примере показано создание связанного сервера с именем SEATTLESales. Название продукта — SQL Server, имя поставщика не используется.

USE master;  
GO  
EXEC sp_addlinkedserver   
   N'SEATTLESales',  
   N'SQL Server';  
GO  

В следующем примере создается связанный сервер S1_instance1 на экземпляре SQL Server с помощью драйвера SQL Server OLE DB.

EXEC sp_addlinkedserver     
   @server=N'S1_instance1',   
   @srvproduct=N'',  
   @provider=N'MSOLEDBSQL',   
   @datasrc=N'S1\instance1';  

В следующем примере создается связанный сервер S1_instance1 на экземпляре SQL Server с помощью поставщика SQL Server Native Client OLE DB.

Важно!

Собственный клиент OLE DB для SQL Server (SQLNCLI) объявляется нерекомендуемым для новых разработок. Вместо этого используйте новый драйвер Microsoft OLE DB для SQL Server (MSOLEDBSQL), который будет обновлен с самыми последними серверными компонентами.

EXEC sp_addlinkedserver     
   @server=N'S1_instance1',   
   @srvproduct=N'',  
   @provider=N'SQLNCLI',   
   @datasrc=N'S1\instance1';  

Б. Использование поставщика Microsoft OLE DB для Microsoft Access

Поставщик Microsoft.Jet.OLEDB.4.0 соединяется с базами данных Microsoft Access в формате 2002–2003. В следующем примере показано создание связанного сервера с именем SEATTLE Mktg.

Примечание

В этом примере предполагается, что установлен и Microsoft Access, и образец Northwind базы данных, а база данных находится в каталоге C:\Msoffice\Access\Samples на том же сервере, что Northwind и экземпляр SQL Server.

EXEC sp_addlinkedserver   
   @server = N'SEATTLE Mktg',   
   @provider = N'Microsoft.Jet.OLEDB.4.0',   
   @srvproduct = N'OLE DB Provider for Jet',  
   @datasrc = N'C:\MSOffice\Access\Samples\Northwind.mdb';  
GO  

В. Использование поставщика Microsoft OLE DB для ODBC с параметром data_source

В следующем примере создается связанный сервер с именем SEATTLE Payroll , который использует поставщик Microsoft OLE DB для ODBC (MSDASQL) и параметр data_source .

Примечание

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

EXEC sp_addlinkedserver   
   @server = N'SEATTLE Payroll',   
   @srvproduct = N'',  
   @provider = N'MSDASQL',   
   @datasrc = N'LocalServer';  
GO  

D. Использование поставщика Microsoft OLE DB для электронной таблицы Excel

Чтобы создать определение связанного сервера с помощью поставщика Microsoft OLE DB для Jet для доступа к электронной таблице Excel в формате 1997–2003, сначала создайте именованный диапазон в Excel, указав столбцы и строки листа Excel для выбора. Затем на имя диапазона можно будет ссылаться в распределенном запросе как на имя таблицы.

EXEC sp_addlinkedserver 'ExcelSource',  
   'Jet 4.0',  
   'Microsoft.Jet.OLEDB.4.0',  
   'c:\MyData\DistExcl.xls',  
   NULL,  
   'Excel 5.0';  
GO  

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

SELECT *  
   FROM ExcelSource...SalesData;  
GO  

Если SQL Server выполняется под учетной записью домена, которая имеет доступ к удаленной общей папке, вместо сопоставленного диска можно использовать UNC-путь.

EXEC sp_addlinkedserver 'ExcelShare',  
   'Jet 4.0',  
   'Microsoft.Jet.OLEDB.4.0',  
   '\\MyServer\MyShare\Spreadsheets\DistExcl.xls',  
   NULL,  
   'Excel 5.0';  

Е. Доступ к текстовому файлу с помощью поставщика Microsoft OLE DB для Jet

Данный пример показывает, как создать связанный сервер для прямого доступа к текстовым файлам без соединения с ними как с таблицами MDB-файла СУБД Access. Поставщик Microsoft.Jet.OLEDB.4.0 и строка поставщика Text.

Источник данных — это полный путь к каталогу, который содержит тестовые файлы. Файл schema.ini, который описывает структуру текстовых файлов, должен находиться в том же каталоге, что и текстовые файлы. Дополнительные сведения о создании файла schema.ini см. в документации по ядру СУБД Jet.

Сначала создайте связанный сервер.

EXEC sp_addlinkedserver txtsrv, N'Jet 4.0',   
   N'Microsoft.Jet.OLEDB.4.0',  
   N'c:\data\distqry',  
   NULL,  
   N'Text';  

Настройте сопоставления имен входа.

EXEC sp_addlinkedsrvlogin txtsrv, FALSE, Admin, NULL;  

Вывод списка таблиц на связанном сервере.

EXEC sp_tables_ex txtsrv;  

Запросите одну из таблиц в данном случае file1#txt, используя четырехкомпонентное имя.

SELECT * FROM txtsrv...[file1#txt];  

F. Использование поставщик OLE DB для DB2 (Майкрософт)

В следующем примере показано создание связанного сервера с именем DB2, который использует Microsoft OLE DB Provider for DB2.

EXEC sp_addlinkedserver  
   @server=N'DB2',  
   @srvproduct=N'Microsoft OLE DB Provider for DB2',  
   @catalog=N'DB2',  
   @provider=N'DB2OLEDB',  
   @provstr=N'Initial Catalog=PUBS;  
       Data Source=DB2;  
       HostCCSID=1252;  
       Network Address=XYZ;  
       Network Port=50000;  
       Package Collection=admin;  
       Default Schema=admin;';  

G. Добавление базы данных Azure SQL в качестве связанного сервера для использования с распределенными запросами в облачных и локальных базах данных

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

Продукт SQL Server box содержит функцию распределенного запроса, которая позволяет создавать запросы для объединения данных из локальных источников данных и данных из удаленных источников (включая данные из источников данных, отличных от SQL Server), определенных как связанные серверы. Каждую базу данных Azure SQL (за исключением базы данных логического сервераmaster) можно добавить в качестве отдельного связанного сервера, а затем использовать непосредственно в приложениях базы данных как любую другую базу данных.

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

Ниже приведен простой пример подключения к базе данных Azure SQL с помощью распределенных запросов.

Сначала добавьте одну базу данных Azure SQL в качестве связанного сервера с помощью SQL Server Native Client.

EXEC sp_addlinkedserver  
  @server='LinkedServerName', 
  @srvproduct='',       
  @provider='sqlncli', 
  @datasrc='ServerName.database.windows.net',   
  @location='',  
  @provstr='',  
  @catalog='DatabaseName'; 

Добавьте учетные данные и параметры на этот связанный сервер.

EXEC sp_addlinkedsrvlogin  
  @rmtsrvname = 'LinkedServerName',  
  @useself = 'false',  
  @rmtuser = 'LoginName',
  @rmtpassword = 'myPassword';

EXEC sp_serveroption 'LinkedServerName', 'rpc out', true;  

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

EXEC ('CREATE TABLE SchemaName.TableName(col1 int not null CONSTRAINT PK_col1 PRIMARY KEY CLUSTERED (col1) )') at LinkedServerName;  
EXEC ('INSERT INTO SchemaName.TableName VALUES(1),(2),(3)') at LinkedServerName; 

Запрос данных с помощью четырехкомпонентных имен:

SELECT * FROM LinkedServerName.DatabaseName.SchemaName.TableName; 

H. Создание связанного сервера с Управляемым экземпляром SQL с помощью аутентификации Azure AD с использованием управляемого удостоверения

Чтобы создать связанный сервер с проверкой подлинности с управляемым удостоверением, выполните следующую команду T-SQL. Метод проверки подлинности используется ActiveDirectoryMSI в параметре @provstr . При необходимости рассмотрите возможность использования @locallogin = NULL , чтобы разрешить все локальные имена входа.

EXEC master.dbo.sp_addlinkedserver
@server     = N'MyLinkedServer',
@srvproduct = N'',
@provider   = N'MSOLEDBSQL',
@provstr    = N'Server=mi.35e5bd1a0e9b.database.windows.net,1433;Authentication=ActiveDirectoryMSI;';

EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'MyLinkedServer',
@useself    = N'False',
@locallogin = N'user1@domain1.com';  

Если Управляемый экземпляр SQL Azure управляемое удостоверение (прежнее название — управляемое удостоверение службы) добавляется в качестве имени входа в удаленный управляемый экземпляр, то проверка подлинности управляемого удостоверения возможна с помощью связанного сервера, созданного в предыдущем примере. Поддерживаются управляемые удостоверения, назначенные системой и управляемые удостоверения, назначаемые пользователем.

Если задано основное удостоверение, оно будет использоваться, в противном случае будет использоваться управляемое удостоверение, назначаемое системой. Если управляемое удостоверение повторно создано с тем же именем, необходимо повторно создать имя входа в удаленном экземпляре, так как новый идентификатор приложения управляемого удостоверения и идентификатор безопасности субъекта-службы Управляемый экземпляр больше не совпадают. Чтобы проверить соответствие этих двух значений, преобразуйте идентификатор sid в идентификатор приложения с помощью следующего запроса.

SELECT convert(uniqueidentifier, sid) as AADApplicationID
FROM sys.server_principals
WHERE name = '<managed_instance_name>';

I. Создание связанного сервера с Управляемым экземпляром SQL с помощью сквозной аутентификации Azure AD

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

EXEC master.dbo.sp_addlinkedserver
@server     = N'MyLinkedServer',
@srvproduct = N'',
@provider   = N'MSOLEDBSQL',
@datasrc    = N'Server=mi.35e5bd1a0e9b.database.windows.net,1433';

При сквозной проверке подлинности контекст безопасности локального имени входа переносится в удаленный экземпляр. Для сквозной проверки подлинности требуется добавить субъект AAD в качестве имени входа в локальный и удаленный Управляемый экземпляр SQL Azure. Оба управляемых экземпляра должны находиться в группе доверия сервера. При соблюдении требований пользователь может войти в локальный экземпляр и запросить удаленный экземпляр через связанный серверный объект.

См. также раздел