sp_addlinkedserver (Transact-SQL)

Область применения: SQL Server Управляемый экземпляр SQL Azure

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

Соглашения о синтаксисе 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, вы можете столкнуться с непредвиденным поведением.

[ @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 значения параметров, которые будут использоваться для настройки связанного сервера.

¹ Этот способ настройки связанного сервера заставляет имя связанного сервера совпадать с сетевым именем удаленного экземпляра 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 не может выполняться в рамках определяемой пользователем транзакции.

Разрешения

Для 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.

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.

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 .

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

Чтобы создать связанный сервер с проверкой подлинности управляемого удостоверения, выполните следующий 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. Оба управляемых экземпляра должны находиться в группе доверия сервера. При выполнении требований пользователь может войти в локальный экземпляр и запросить удаленный экземпляр с помощью связанного объекта сервера.

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

• Хранимые процедуры распределенных запросов (Transact-SQL)
• sp_addlinkedsrvlogin (Transact-SQL)
• sp_addserver (Transact-SQL)
• sp_dropserver (Transact-SQL)
• sp_serveroption (Transact-SQL)
• sp_setnetname (Transact-SQL)
• Системные хранимые процедуры (Transact-SQL)
• Системные таблицы (Transact-SQL)