sp_addlinkedserver(Transact-SQL)

연결된 서버를 만듭니다. 연결된 서버를 만들면 OLE DB 데이터 원본과 유형이 다른 분산 쿼리에 액세스할 수 있습니다. sp_addlinkedserver를 사용하여 연결된 서버를 만든 후 이 서버에 대해 분산 쿼리를 실행할 수 있습니다. 연결된 서버를 SQL Server 인스턴스로 정의한 경우에는 원격 저장 프로시저를 실행할 수 있습니다.

Transact-SQL 구문 표기 규칙

구문

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

인수

• [ @server= ] 'server'
  만들려고 하는 연결된 서버의 이름입니다. server는 sysname이며 기본값은 없습니다.

• [ @srvproduct= ] 'product_name'
  연결된 서버로 추가할 OLE DB 데이터 원본의 제품 이름입니다. product_name은 nvarchar(128)이며 기본값은 NULL입니다. SQL Server의 경우 provider_name, data_source, location, provider_string, catalog를 지정할 필요가 없습니다.

• [ @provider= ] 'provider_name'
  이 데이터 원본에 해당하는 OLE DB 공급자의 고유 PROGID(프로그래밍 ID)입니다. provider_name은 현재 컴퓨터에 설치된 지정된 OLE DB 공급자에 대해 고유해야 합니다. provider_name은 nvarchar(128)이며 기본값은 NULL입니다. provider_name을 생략하면 SQLNCLI가 사용됩니다. SQLNCLI를 사용하면 SQL Server가 최신 버전의 SQL Server Native Client OLE DB 공급자로 리디렉션됩니다. OLE DB 공급자는 지정한 PROGID와 함께 레지스트리에 등록됩니다.

• [ @datasrc= ] 'data_source'
  OLE DB 공급자가 해석하는 데이터 원본의 이름입니다. data_source는 nvarchar(4000)입니다. data_source는 OLE DB 공급자를 초기화하기 위해 DBPROP_INIT_DATASOURCE 속성으로 전달됩니다.

• [ @location= ] 'location'
  OLE DB 공급자에 의해 해석된 데이터베이스의 위치입니다. location은 nvarchar(4000)이며 기본값은 NULL입니다. location은 DBPROP_INIT_LOCATION 속성으로 전달되어 OLE DB 공급자를 초기화합니다.

• [ @provstr= ] 'provider_string'
  고유한 데이터 원본을 나타내는 OLE DB 공급자의 연결 문자열입니다. provider_string은 nvarchar(4000)이며 기본값은 NULL입니다. provstr은 IDataInitialize로 전달되거나 DBPROP_INIT_PROVIDERSTRING 속성으로 설정되어 OLE DB 공급자를 초기화합니다.

  SQL Server Native Client OLE DB 공급자에 대해 연결된 서버를 만들 때는 SERVER 키워드를 SERVER=servername\instancename으로 사용하여 SQL Server의 특정 인스턴스를 지정함으로써 인스턴스를 지정할 수 있습니다. servername은 SQL Server가 실행되는 컴퓨터의 이름이고 instancename은 사용자가 연결할 SQL Server의 인스턴스입니다.

  [!참고]

  미러된 데이터베이스에 액세스하려면 연결 문자열이 데이터베이스 이름을 포함해야 합니다. 이 이름은 데이터 액세스 공급자의 장애 조치(Failover) 시도를 지원하는 데 필요합니다. 데이터베이스를 @provstr 또는 @catalog 매개 변수에 지정할 수 있습니다. 필요에 따라 연결 문자열이 장애 조치 파트너 이름을 제공할 수도 있습니다. 자세한 내용은 데이터베이스 미러링 세션에 대한 초기 연결 설정을 참조하십시오.

• [ @catalog= ] 'catalog'
  OLE DB 공급자에 연결할 때 사용되는 카탈로그입니다. catalog는 sysname이며 기본값은 NULL입니다. catalog는 DBPROP_INIT_CATALOG 속성으로 전달되어 OLE DB 공급자를 초기화합니다. SQL Server 인스턴스에 대해 연결된 서버를 정의한 경우 카탈로그는 연결된 서버가 매핑된 기본 데이터베이스를 참조합니다.

반환 코드 값

0(성공) 또는 1(실패)

결과 집합

없음

주의

다음 표에서는 OLE DB를 통해 액세스할 수 있는 데이터 원본에 대해 연결된 서버를 설정하는 방법을 보여 줍니다. 특정 데이터 원본에 대해 여러 가지 방법을 사용하여 연결된 서버를 설정할 수 있습니다. 따라서 데이터 원본 유형에 대한 행이 여러 개 있을 수 있습니다. 또한 이 표에서는 연결된 서버를 설정하는 데 사용되는 sp_addlinkedserver 매개 변수 값을 보여 줍니다.

원격 OLE DB 데이터 원본	OLE DB 공급자	product_name	provider_name	data_source	location	provider_string	catalog
SQL Server	MicrosoftSQL Server Native Client OLE DB 공급자	SQL Server1(기본값)
SQL Server	MicrosoftSQL Server Native Client OLE DB 공급자		SQLNCLI	기본 인스턴스에 대한 SQL Server의 네트워크 이름			데이터베이스 이름(옵션)
SQL Server	MicrosoftSQL Server Native Client OLE DB 공급자		SQLNCLI	특정 인스턴스에 대한 servername\instancename			데이터베이스 이름(옵션)
Oracle	Microsoft OLE DB Provider for Oracle	임의의 값2	MSDAORA	Oracle 데이터베이스의 SQL*Net 별칭
Oracle 버전 8 이상	Oracle Provider for OLE DB	임의의 값	OraOLEDB.Oracle	Oracle 데이터베이스의 별칭
Access/Jet	Microsoft OLE DB Provider for Jet	임의의 값	Microsoft.Jet.OLEDB.4.0	Jet 데이터베이스 파일의 전체 경로
ODBC 데이터 원본	Microsoft OLE DB Provider for ODBC	임의의 값	MSDASQL	ODBC 데이터 원본의 시스템 DSN
ODBC 데이터 원본	Microsoft OLE DB Provider for ODBC	임의의 값	MSDASQL			ODBC 연결 문자열
파일 시스템	Microsoft OLE DB Provider for Indexing Service	임의의 값	MSIDXS	인덱싱 서비스 카탈로그 이름
Microsoft Excel 스프레드시트	Microsoft OLE DB Provider for Jet	임의의 값	Microsoft.Jet.OLEDB.4.0	Excel 파일의 전체 경로		Excel 5.0
IBM DB2 데이터베이스	Microsoft OLE DB Provider for DB2	임의의 값	DB2OLEDB			Microsoft OLE DB Provider for DB2 설명서를 참조하십시오.	DB2 데이터베이스의 카탈로그 이름

1 연결된 서버를 이 방법으로 설정하면 연결된 서버의 이름이 SQL Server 원격 인스턴스의 네트워크 이름과 동일하게 됩니다. 서버를 지정하려면 data_source를 사용하십시오.

2 "임의의 값"은 제품 이름으로 어떤 것이든 사용할 수 있음을 나타냅니다.

MicrosoftSQL Server Native Client OLE DB 공급자는 공급자 이름을 지정하지 않은 경우 또는 제품 이름으로 SQL Server를 지정한 경우 SQL Server에 사용되는 공급자입니다. 이 공급자의 이전 이름인 SQLOLEDB를 지정하더라도 카탈로그에 보관될 때는 SQLNCLI로 변경됩니다.

data_source, location, provider_string, catalog 매개 변수는 연결된 서버가 가리키는 데이터베이스를 나타냅니다. 이러한 매개 변수 중 하나가 NULL이면 해당되는 OLE DB 초기화 속성이 설정되지 않습니다.

클러스터형 환경에서 OLE DB 데이터 원본을 가리키는 파일 이름을 지정할 때는 UNC(Universal Naming Convention) 이름이나 공유 드라이브를 사용하여 위치를 지정하십시오.

사용자 정의 트랜잭션 내에서는 sp_addlinkedserver를 실행할 수 없습니다.

보안 정보
sp_addlinkedserver를 사용하여 연결된 서버를 만들면 기본 자체 매핑이 모든 로컬 로그인에 대해 추가됩니다. SQL Server 이외 공급자를 사용하는 경우에는 SQL Server 인증 로그인이 SQL Server 서비스 계정으로 공급자에 액세스할 수도 있습니다. 이와 같은 경우 관리자는 sp_droplinkedsrvlogin <linkedserver_name>, NULL을 사용하여 전역 매핑을 제거해야 합니다.

사용 권한

ALTER ANY LINKED SERVER 권한이 필요합니다.

예

1. Microsoft SQL Server Native Client OLE DB Provider 사용

다음 예에서는 SEATTLESales라는 연결된 서버를 만듭니다. 제품 이름은 SQL Server이고 공급자 이름은 사용하지 않았습니다.

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

다음 예에서는 SQL Server Native Client OLE DB 공급자를 사용하여 SQL Server 인스턴스에 S1_instance1이라는 연결된 서버를 만듭니다.

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

2. Microsoft OLE DB Provider for Microsoft Access 사용

Microsoft.Jet.OLEDB.4.0 공급자는 2002-2003 형식을 사용하는 Microsoft Access 데이터베이스에 연결합니다. 다음 예에서는 SEATTLE Mktg라는 연결된 서버를 만듭니다.

[!참고]

이 예에서는 Microsoft Access와 예제 Northwind 데이터베이스가 모두 설치되어 있으며, Northwind 데이터베이스가 C:\Msoffice\Access\Samples에 있다고 가정합니다.

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

Microsoft.ACE.OLEDB.12.0 공급자는 2007 형식을 사용하는 Microsoft Access 데이터베이스에 연결합니다. 다음 예에서는 SEATTLE Mktg라는 연결된 서버를 만듭니다.

[!참고]

이 예에서는 Microsoft Access와 예제 Northwind 데이터베이스가 모두 설치되어 있으며, Northwind 데이터베이스가 C:\Msoffice\Access\Samples에 있다고 가정합니다.

EXEC sp_addlinkedserver 
   @server = 'SEATTLE Mktg', 
   @provider = 'Microsoft.ACE.OLEDB.12.0', 
   @srvproduct = 'OLE DB Provider for ACE',
   @datasrc = 'C:\MSOffice\Access\Samples\Northwind.accdb'
GO

Microsoft OLE DB Provider for Oracle 사용

다음 예에서는 Microsoft OLE DB Provider for Oracle을 사용하는 LONDON Mktg라는 연결된 서버를 만들며 Oracle 데이터베이스에 대한 SQL*Net 별칭이 MyServer라고 가정합니다.

EXEC sp_addlinkedserver
   @server = 'LONDON Mktg',
   @srvproduct = 'Oracle',
   @provider = 'MSDAORA',
   @datasrc = 'MyServer'
GO

4. data_source 매개 변수와 함께 Microsoft OLE DB Provider for ODBC 사용

다음 예에서는 Microsoft OLE DB Provider for ODBC(MSDASQL) 및 data_source 매개 변수를 사용하는 SEATTLE Payroll이라는 연결된 서버를 만듭니다.

[!참고]

연결된 서버를 사용하려면 지정한 ODBC 데이터 원본 이름이 서버에서 시스템 DSN으로 정의되어 있어야 합니다.

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

5. Excel 스프레드시트에서 Microsoft OLE DB 공급자 사용

1997 - 2003 형식의 Excel 스프레드시트에 액세스하기 위해 Microsoft OLE DB Provider for Jet을 사용하여 연결된 서버 정의를 만들려면 먼저 선택할 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'

ACE 공급자를 사용하여 Excel 2007 형식의 Excel 스프레드시트에 연결하려면

EXEC sp_addlinkedserver @server = N'ExcelDataSource', 
@srvproduct=N'ExcelData', @provider=N'Microsoft.ACE.OLEDB.12.0', 
@datasrc=N'C:\DataFolder\People.xlsx',
@provstr='EXCEL 12.0' ;

6. Microsoft OLE DB Provider for Jet을 사용하여 텍스트 파일 액세스

다음 예에서는 Access.mdb 파일의 테이블에서처럼 파일을 연결하지 않고 직접 텍스트 파일에 액세스하기 위해 연결된 서버를 만듭니다. 공급자는 Microsoft.Jet.OLEDB.4.0이고 공급자 문자열은 Text입니다.

데이터 원본은 텍스트 파일을 포함하는 디렉터리의 전체 경로 이름입니다. 텍스트 파일의 구조를 정의하는 Schema.ini 파일은 반드시 텍스트 파일과 동일한 디렉터리에 있어야 합니다. Schema.ini 파일을 만드는 방법은 Jet Database Engine 설명서를 참조하십시오.

--Create a linked server.
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0', 
   'Microsoft.Jet.OLEDB.4.0',
   'c:\data\distqry',
   NULL,
   'Text'
GO

--Set up login mappings.
EXEC sp_addlinkedsrvlogin txtsrv, FALSE, Admin, NULL
GO

--List the tables in the linked server.
EXEC sp_tables_ex txtsrv
GO

--Query one of the tables: file1#txt
--using a four-part name. 
SELECT * 
FROM txtsrv...[file1#txt]

7. Microsoft OLE DB Provider for DB2 사용

다음 예에서는 Microsoft OLE DB Provider for DB2를 사용하는 DB2라는 연결된 서버를 만듭니다.

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

참고 항목

참조

분산 쿼리 저장 프로시저(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)

개념

위임용으로 연결된 서버 구성

SQL Server로 테스트한 OLE DB 공급자