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

엔터프라이즈 관리자를 사용하여 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'