sp_addlinkedserver (Transact-SQL)

Мақала
08/21/2024

Область применения: 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 могут получить доступ к поставщику в учетной записи службы 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 Azure с проверкой подлинности управляемого удостоверения

Примечание.

Идентификатор Microsoft Entra ранее был известен как Azure Active Directory (Azure AD).

Чтобы создать связанный сервер с проверкой подлинности управляемого удостоверения, выполните следующий 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 MSEntraApplicationID
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 Azure с проверкой подлинности управляемого удостоверения

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

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

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

Кері байланыс

Қосымша ресурстар

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