sp_addlinkedserver (Transact-SQL)

Статья
11/25/2023

Применимо к:SQL Server Управляемый экземпляр SQL Azure

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

Примечание.

Идентификатор Microsoft Entra — это новое имя Azure Active Directory (Azure AD). В настоящее время мы обновляем документацию.

Соглашения о синтаксисе Transact-SQL

Синтаксис

sp_addlinkedserver
    [ @server = ] N'server'
    [ , [ @srvproduct = ] N'srvproduct' ]
    [ , [ @provider = ] N'provider' ]
    [ , [ @datasrc = ] N'datasrc' ]
    [ , [ @location = ] N'location' ]
    [ , [ @provstr = ] N'provstr' ]
    [ , [ @catalog = ] N'catalog' ]
    [ , [ @linkedstyle = ] linkedstyle ]
[ ; ]

Аргументы

[ @server = ] N'server'

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

[ @srvproduct = ] N'srvproduct'

Имя продукта источника данных OLE DB для добавления в качестве связанного сервера. @srvproduct — nvarchar(128) с значением по умолчаниюNULL. Если значение равно SQL Server, @provider, @datasrc, @location, @provstr и @catalog не нужно указывать.

[ @provider = ] N'provider'

Уникальный программный идентификатор поставщика OLE DB, соответствующий этому источнику данных. @provider должен быть уникальным для указанного поставщика OLE DB, установленного на текущем компьютере. @provider — nvarchar(128) с значением по умолчаниюNULL.

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

Важно!

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

[ @datasrc = ] N'datasrc'

Имя источника данных, интерпретируемого поставщиком OLE DB. @datasrc — nvarchar(4000) с значением по умолчаниюNULL. @datasrc передается в качестве DBPROP_INIT_DATASOURCE свойства для инициализации поставщика OLE DB.

[ @location = ] N'location'

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

[ @provstr = ] N'provstr'

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

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

Чтобы получить доступ к зеркальной базе данных, строка соединения должна содержать имя базы данных. Это имя необходимо, чтобы предоставить поставщику доступа к данным возможность пытаться отработать отказ. Базу данных можно указать в параметре @provstr или @catalog . Кроме того, строка соединения может содержать имя партнера по обеспечению отработки отказа.
При выполнении sp_addlinkedserver из локального имени входа или имени входа, который не входит в роль sysadmin , может появиться следующая ошибка:
```
Access to the remote server is denied because no login-mapping exists.
```
Чтобы устранить эту проблему, добавьте параметр в User ID строка подключения. В следующем примере идентификатор пользователя передается myUser в строка подключения:
```
EXEC master.dbo.sp_addlinkedserver @server = N'LinkServerName',
    @provider = N'SQLNCLI',
    @srvproduct = 'MS SQL Server',
    @provstr = N'SERVER=serverName\InstanceName;User ID=myUser'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LinkServerName',
    @locallogin = NULL,
    @useself = N'False',
    @rmtuser = N'myUser',
    @rmtpassword = N'*****'
```
Дополнительные сведения см. в статье "Доступ к удаленному серверу" запрещен, так как сопоставление имен входа не существует.

[ @catalog = ] N'catalog'

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

[ @linkedstyle = ] linkedstyle

Указано только в ознакомительных целях. Не поддерживается. Совместимость с будущими версиями не гарантируется.

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

0 (успешно) или 1 (сбой).

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

Нет.

Замечания

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

Удаленный источник данных OLE DB	Поставщик OLE DB	@srvproduct	@provider	@datasrc	@provstr	@catalog
SQL Server	Поставщик OLE DB для собственного клиента SQL Server	SQL Server ¹ (по умолчанию)
SQL Server	Поставщик OLE DB для собственного клиента SQL Server		`SQLNCLI`	Сетевое имя SQL Server (для экземпляра по умолчанию)		Имя базы данных (необязательно)
SQL Server	Поставщик OLE DB для собственного клиента SQL Server		`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	поставщик OLE DB для DB2 (Майкрософт)	Любое	`DB2OLEDB`		См. поставщик OLE DB для DB2 (Майкрософт) документацию.	Имя каталога базы данных DB2

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

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

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

Параметры @datasrc, @location, @provstr и @catalog определяют базу данных или базы данных, на которые указывает связанный сервер. Если один из этих параметров имеет NULLзначение, соответствующее свойство инициализации OLE DB не задано.

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

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

Важно!

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

Важно!

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

Разрешения

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

Примеры

А. Использование поставщика OLE DB в Microsoft SQL Server

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

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

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

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

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

Важно!

Поставщик 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';

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

C. Использование поставщика MICROSOFT OLE DB для ODBC с параметром `datasrc`

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

Примечание.

Указанный источник данных 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, использующим поставщик OLE DB для 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. Добавление базы данных SQL Azure в качестве связанного сервера для использования с распределенными запросами в облачных и локальных базах данных

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

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

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

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

Сначала добавьте одну базу данных SQL Azure в качестве связанного сервера, используя собственный клиент SQL Server.

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 связанного сервера с проверкой подлинности Microsoft Entra с управляемым удостоверением

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

EXEC master.dbo.sp_addlinkedserver
    @server = N'MyLinkedServer',
    @srvproduct = N'',
    @provider = N'MSOLEDBSQL',
    @provstr = N'Server=<mi_name>.<dns_zone>.database.windows.net,1433;Authentication=ActiveDirectoryMSI;';

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

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

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

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

I. Создание Управляемый экземпляр SQL связанного сервера с сквозной проверкой подлинности Microsoft Entra

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

EXEC master.dbo.sp_addlinkedserver
    @server = N'MyLinkedServer',
    @srvproduct = N'',
    @provider = N'MSOLEDBSQL',
    @datasrc = N'<mi_name>.<dns_zone>.database.windows.net,1433';

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

sp_addlinkedserver (Transact-SQL)

Синтаксис

Аргументы

[ @server = ] N'server'

[ @srvproduct = ] N'srvproduct'

[ @provider = ] N'provider'

[ @datasrc = ] N'datasrc'

[ @location = ] N'location'

[ @provstr = ] N'provstr'

[ @catalog = ] N'catalog'

[ @linkedstyle = ] linkedstyle

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

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

Замечания

Разрешения

Примеры

А. Использование поставщика OLE DB в Microsoft SQL Server

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

C. Использование поставщика MICROSOFT OLE DB для ODBC с параметром `datasrc`

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

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

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

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

H. Создание Управляемый экземпляр SQL связанного сервера с проверкой подлинности Microsoft Entra с управляемым удостоверением

I. Создание Управляемый экземпляр SQL связанного сервера с сквозной проверкой подлинности Microsoft Entra

Обратная связь

Обратная связь

Дополнительные ресурсы

Дополнительные ресурсы

sp_addlinkedserver (Transact-SQL)

Синтаксис

Аргументы

[ @server = ] N'server'

[ @srvproduct = ] N'srvproduct'

[ @provider = ] N'provider'

[ @datasrc = ] N'datasrc'

[ @location = ] N'location'

[ @provstr = ] N'provstr'

[ @catalog = ] N'catalog'

[ @linkedstyle = ] linkedstyle

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

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

Замечания

Разрешения

Примеры

А. Использование поставщика OLE DB в Microsoft SQL Server

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

C. Использование поставщика MICROSOFT OLE DB для ODBC с параметром datasrc

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

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

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

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

H. Создание Управляемый экземпляр SQL связанного сервера с проверкой подлинности Microsoft Entra с управляемым удостоверением

I. Создание Управляемый экземпляр SQL связанного сервера с сквозной проверкой подлинности Microsoft Entra

Связанный контент

Обратная связь

Обратная связь

Дополнительные ресурсы

Дополнительные ресурсы

C. Использование поставщика MICROSOFT OLE DB для ODBC с параметром `datasrc`