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_name равно nvarchar(128) со значением по умолчанию NULL. Если значение равно SQL Server, указывать provider_name, data_source, расположение, provider_string и каталог не требуется.

[ @provider = ] 'provider_name'

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

  • До SQL Server 2022 (16.x) используется SQLNCLI, если @provider этот параметр опущен. При использовании SQLNCLI SQL Server будет использовать последнюю версию поставщика OLE DB для собственного клиента SQL Server. Предполагается, что поставщик OLE DB будет зарегистрирован в реестре с указанным идентификатором PROGID. Вместо SQLNCLI рекомендуется использовать MSOLEDBSQL.
  • Начиная с SQL Server 2022 (16.x) необходимо указать имя поставщика. Рекомендуется использовать MSOLEDBSQL.

Важно!

SQL Server Native Client (часто сокращенный SNAC) был удален из SQL Server 2022 (16.x) и SQL Server Management Studio 19 (SSMS). Для разработки не рекомендуется использовать поставщик SQL Server Native Client OLE DB (SQLNCLI или SQLNCLI11) и устаревший поставщик MICROSOFT OLE DB для SQL Server (SQLOLEDB). Перейдите на новый драйвер Microsoft OLE DB (MSOLEDBSQL) для SQL Server в будущем.

[ @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 asSERVER=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 Поставщик MICROSOFT SQL Server Native Client OLE DB SQL Server 1 (по умолчанию)
SQL Server Поставщик MICROSOFT SQL Server Native Client OLE DB SQLNCLI Сетевое имя SQL Server (для экземпляра по умолчанию) Имя базы данных (необязательно)
SQL Server Поставщик MICROSOFT SQL Server Native Client OLE DB 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 "Любой" означает, что название продукта может быть любым.

Поставщик Microsoft SQL Server Native Client OLE DB — это поставщик, который используется с 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. Использование поставщика MICROSOFT SQL Server OLE DB

В следующем примере показано создание связанного сервера с именем 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  

Г. Использование электронной таблицы поставщика 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];  

Е. Использование поставщик 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;';  

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

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

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

Преимущества использования Azure SQL Базы данных включают управляемость, высокий уровень доступности, масштабируемость, работу с знакомой моделью разработки и модель реляционных данных. Требования к приложению базы данных определяют, как оно будет использовать Azure SQL Database в облаке. Вы можете переместить все данные одновременно в базу данных 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; 

З. Создание связанного сервера с Управляемым экземпляром 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>';

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

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

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

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

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