SQL Server 연결된 서버 및 분산 쿼리에서 Excel을 사용하는 방법

기술 자료 번역 기술 자료 번역
기술 자료: 306397
모두 확대 | 모두 축소

이 페이지에서

요약

SQL Server에서는 다른 OLE DB 데이터 원본과의 임시 연결 또는 영구 연결을 지원합니다. 영구 연결을 연결된 서버라고 하며, 한 번의 쿼리를 위해 구성된 임시 연결을 분산 쿼리라고 합니다.

Microsoft Excel 통합 문서는 이러한 방식으로 SQL Server를 통해 쿼리할 수 있는 OLE DB 데이터 원본 중 하나입니다. 이 문서에서는 Excel 데이터 원본을 연결된 서버로 구성하는 데 필요한 구문 및 Excel 데이터 원본을 쿼리하는 분산 쿼리를 사용하는 데 필요한 구문에 대해 설명합니다.

추가 정보

Excel을 연결된 서버로 쿼리

엔터프라이즈 관리자, 시스템 저장 프로시저 또는 SQL-DMO(분산 관리 개체)를 사용하여 Excel 데이터 원본을 SQL Server 연결된 서버로 구성할 수 있습니다. 어떤 방법을 사용하든지 다음 네 가지 속성을 항상 설정해야 합니다.
  • 연결된 서버에 사용할 이름
  • 연결에 사용할 OLE DB 공급자
  • 데이터 원본 또는 Excel 통합 문서의 전체 경로와 파일 이름
  • 대상을 Excel 통합 문서로 식별하는 공급자 문자열. Jet 공급자는 대상을 기본적으로 Access 데이터베이스로 간주합니다.
시스템 저장 프로시저 sp_addlinkedserver를 사용할 경우에는 @srvproduct 속성도 설정해야 합니다. 이 속성에는 문자열 값을 설정할 수 있습니다.

엔터프라이즈 관리자를 사용하여 Excel 연결된 서버 구성

  1. 엔터프라이즈 관리자에서 보안 폴더를 눌러 확장합니다.
  2. 연결된 서버를 마우스 오른쪽 단추로 누른 다음 새 연결된 서버를 누릅니다.
  3. 일반 탭에서 다음 단계를 수행합니다.
    1. 연결된 서버의 이름을 첫 번째 텍스트 상자에 입력합니다.
    2. 서버 유형 텍스트 상자에서 기타 데이터 원본을 누릅니다.
    3. 공급자 이름 드롭다운 목록 상자에서 Microsoft Jet 4.0 OLE DB Provider를 누릅니다.
    4. 데이터 원본 텍스트 상자에 Excel 파일의 전체 경로와 파일 이름을 입력합니다.
    5. Excel 97, Excel 2000 또는 Excel 2002 통합 문서를 의미하는 Excel 8.0공급자 문자열 텍스트 상자에 입력합니다.
    6. 확인을 눌러 새 연결된 서버를 만듭니다.
  4. 새로 만든 연결된 서버의 이름을 눌러 확장하고 그 아래의 개체 목록을 확장합니다.
  5. 새 연결된 서버 이름 아래에서 테이블을 누릅니다. 오른쪽 창에 워크시트와 명명된 범위가 나타납니다.

저장 프로시저를 사용하여 Excel 연결된 서버 구성

시스템 저장 프로시저 sp_addlinkedserver를 사용하여 Excel 데이터 원본을 연결된 서버로 구성할 수도 있습니다.
DECLARE @RC int
DECLARE @server nvarchar(128)
DECLARE @srvproduct nvarchar(128)
DECLARE @provider nvarchar(128)
DECLARE @datasrc nvarchar(4000)
DECLARE @location nvarchar(4000)
DECLARE @provstr nvarchar(4000)
DECLARE @catalog nvarchar(128)
-- Set parameter values
SET @server = 'XLTEST_SP'
SET @srvproduct = 'Excel'
SET @provider = 'Microsoft.Jet.OLEDB.4.0'
SET @datasrc = 'c:\book1.xls'
SET @provstr = 'Excel 8.0'
EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider, 
@datasrc, @location, @provstr, @catalog
				
위에 나와 있는 것처럼 이 저장 프로시저에는 @srvproduct 인수에 대한 임의의 문자열 값이 추가로 필요합니다. 이 인수는 엔터프라이즈 관리자 구성에 "제품 이름"으로 나타납니다. @location@catalog 인수는 사용되지 않습니다.

SQL-DMO를 사용하여 Excel 연결된 서버 구성

SQL 분산 관리 개체를 사용하면 Microsoft Visual Basic이나 다른 프로그래밍 언어에서 프로그래밍 방식으로 Excel 데이터 원본을 연결된 서버로 구성할 수 있습니다. 엔터프라이즈 관리자 구성에 필요한 것과 동일한 네 가지 인수를 제공해야 합니다.
Private Sub Command1_Click()
    Dim s As SQLDMO.SQLServer
    Dim ls As SQLDMO.LinkedServer
    Set s = New SQLDMO.SQLServer
    s.Connect "(local)", "sa", "password"
    Set ls = New SQLDMO.LinkedServer
    With ls
        .Name = "XLTEST_DMO"
        .ProviderName = "Microsoft.Jet.OLEDB.4.0"
        .DataSource = "c:\book1.xls"
        .ProviderString = "Excel 8.0"
    End With
    s.LinkedServers.Add ls
    s.Close
End Sub
				

Excel 연결된 서버 쿼리

Excel 데이터 원본을 연결된 서버로 구성한 후 쿼리 분석기나 다른 클라이언트 응용 프로그램에서 데이터를 간편하게 쿼리할 수 있습니다. 예를 들어, Excel 파일의 Sheet1에 저장된 데이터 행을 검색하기 위해 아래 코드에서는 SQL-DMO를 사용하여 구성한 연결된 서버를 사용합니다.
SELECT * FROM XLTEST_DMO...Sheet1$
				
다음과 같이 OPENQUERY를 사용하여 Excel 연결된 서버를 "통과(passthrough)" 방식으로 쿼리할 수도 있습니다.
SELECT * FROM OPENQUERY(XLTEST_DMO, 'SELECT * FROM [Sheet1$]')
				
OPENQUERY에 제공해야 하는 첫 번째 인수는 연결된 서버 이름입니다. 위와 같이 워크시트 이름은 구분 기호를 통해 구별해 주어야 합니다.

아래의 쿼리를 사용하여 Excel 연결된 서버에 있는 모든 테이블 목록을 가져올 수도 있습니다.
EXECUTE SP_TABLES_EX 'XLTEST_DMO'
				

분산 쿼리를 사용하여 Excel 쿼리

아래와 같이 SQL Server 분산 쿼리와 OPENDATASOURCE 또는 OPENROWSET 함수를 사용하여 자주 액세스하지 않는 Excel 데이터 원본을 임시로 쿼리할 수 있습니다.
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
   'Data Source=c:\book1.xls;Extended Properties=Excel 8.0')...Sheet1$
				
OPENROWSET에서는 두 번째 인수("공급자 문자열")에 대해 보통 때와는 다른 구문을 사용합니다.
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
   'Excel 8.0;Database=c:\book1.xls', Sheet1$)
				
ADO(ActiveX Data Objects) 개발자의 경우 OPENROWSET의 두 번째 인수("문자열 공급자")에 다음과 같은 구문을 사용하려고 할 수도 있을 것입니다.
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
   'Data Source=c:\book1.xls;Extended Properties=Excel 8.0', Sheet1$)
				
그러나 이렇게 하면 Jet 공급자에서 다음과 같은 오류가 발생합니다.
설치 가능한 ISAM을 찾을 수 없습니다.

참조

SQL Server 연결된 서버 및 분산 쿼리에서는 OLE DB 공급자를 사용하므로 ADO와 Excel을 함께 사용할 때의 일반적인 지침과 주의 사항이 동일하게 적용됩니다. 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.
257819 Visual Basic 또는 VBA에서 Excel 데이터에 ADO를 사용하는 방법




Microsoft 제품 관련 기술 전문가들과 온라인으로 정보를 교환하시려면 Microsoft 뉴스 그룹에 참여하시기 바랍니다.

속성

기술 자료: 306397 - 마지막 검토: 2011년 2월 15일 화요일 - 수정: 3.3
키워드:?
kbsqlmanagementtools kbdatabase kbhowto kbjet KB306397

피드백 보내기

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com