엑셀 워크시트를 연결된 서버로 만들기

TPPsc님의 블로그 | 태쥐
http://blog.naver.com/tppsc/60028037516

오랜만에 강좌를 올리게 되네요. 예전에 연결된 서버(Linked Server) 마지막 강좌에서 '다음 강좌를 기대해 주세요'라고 했는데, 그 이후로 많은 시간이 흘렀습니다. 그 강좌에 이어 연결된 서버의 활용에 대해 설명을 해보고자 합니다.

연결된 서버는 꼭 서버와 서버 간에만 가능한것이 아닙니다. 바로 이전 강좌 마지막 부분에 언급 했듯이 엑셀 워크시트를 연결된 서버로 설정할 수도 있고, 일반 텍스트 문서를 연결된 서버로 설정할 수 있습니다. 이번 강좌에서는 엑셀 워크시트를 연결된 서버로 설정하여 엑셀 워크시트를 데이터베이스의 테이블처럼 활용하는 방법을 확인해 보도록 하겠습니다.

1. 엑셀 워크시트 준비

우선 작업에 사용할 엑셀 워크시트를 만들어 보도록 하겠습니다. 다음 [그림 1]과 같이 간단한 연락처 목록을 만들어 C:\temp 폴더에 저장을 했습니다.

[그림 1]

이름, 전화번호, 주소, 성별 이렇게 네개의 컬럼으로 구성된 연락처입니다. 사실 업무에서 이렇게 간단한 엑셀 워크시트를 사용할 일은 없습니다. 단지 예를 들기 위해 이렇게 작성했음을 양해해 주시기 바랍니다. 위 엑셀 시트를 연결된 서버로 설정하는 방법은 다음과 같습니다.

2. 연결된 서버 만들기

연결된 서버를 만드는 것은 바로 이전 강좌에서 설명이 된 내용입니다. EM에서 연결된 서버를 등록하는 부분은 다음 [그림 2]와 같이 [보안] 부분의 [연결된 서버] 부분 입니다. 현재는 연결된 서버가 전혀 등록되어 있지 않음을 알 수 있습니다.

[그림 2]

[그림 2]의 "연결된 서버" 에서 마우스 오른쪽 버튼을 눌러 표시되는 단축 메뉴에서 "새 연결된 서버(S)"를 선택하면 다음 [그림 3]과 같이 "연결된 서버 속성" 대화 창이 표시됩니다.

[그림 3]

이제부터 설명하는 내용이 중요합니다. 엑셀 시트를 연결된 서버로 설정하기 위해서는 다음 [그림 4]와 같이 입력을 해주어야 합니다.

[그림 4]

ㅇ 연결된 서버(N) : 이후에 우리가 사용할 이름을 지정합니다. 여기서는 EXCEL로 입력하겠습니다.
ㅇ 공급자 이름(P) : Microsoft Jet 4.0 OLE DB Provider
ㅇ 제품 이름(U) : Jet 4.0
ㅇ 데이터 원본(D) : 앞에서 만든 엑셀 워크시트의 경로 및 파일 이름을 지정합니다. 우리가 앞에서 작성한 엑셀 워크시트는 c:\temp\연락처.xls 입니다.
ㅇ 공급자 문자열(V) : 엑셀 버젼을 입력합니다. 대부분 Excel 8.0으로 입력하면 될거라 보입니다.

그리고 아래 [그림 5]와 같이 엑셀 워크시트에 연결할 때 사용할 계정을 등록해 줍니다. 계정 부분에 admin을 입력해 주시고 암호는 비워두시기 바랍니다.

[그림 5]

[확인] 버튼을 누르면 여기 까지의 과정으로 해서 엑셀 워크시트를 연결된 서버로 설정하는 과정이 완료 된 것입니다. EM에서 등록된 Excel 이라는 이름의 연결된 서버를 확장하고 테이블 부분을 선택하면 아래 [그림 6]과 같이 엑셀 워크시트에서 보이던 세개의 시트가 보일 겁니다.

[그림 6]

위 결과를 보더라도 뭔가 된것 같은 느낌이 들지 않나요? 자 그럼 QA에서 연결한 엑셀 워크시트의 내용을 검색해 보도록 하겠습니다.

3. 연결된 서버 사용하기

QA를 연결한 후 아래 [그림 7]과 같이 SELECT 문을 수행하게 되면 엑셀 워크시트의 내용이 표시되는것을 볼 수 있습니다.

[그림 7]

수행한 쿼리문은 다음과 같습니다.

SELECT * FROM EXCEL...sheet1$

ㅇ 연결된 서버 이름 EXCEL을 사용했습니다.
ㅇ 데이터베이스와 소유자를 생각하기 위해 ... 를 입력했습니다.
ㅇ sheet$1은 데이터가 입력되어 있는 첫번째 시트 이름입니다.

위 결과처럼 이제 엑셀 워크시트를 데이터베이스의 테이블처럼 사용할 수 있게 되었습니다. 그런데 출력된 결과가 저희가 입력한 순서랑 맞지가 않네요. 다음과 같이 해 주어야 할 것 같습니다.

SELECT 이름, 전화번호, 주소, 성별 FROM EXCEL...sheet1$

다음과 같이 데이터 추가도 가능합니다.

INSERT INTO EXCEL...sheet1$(이름, 전화번호, 주소, 성별)
VALUES('한국인', '666-8877', '부산', '남')

또한 다음과 같이 데이터 변경도 가능합니다.

UPDATE EXCEL...sheet1$
SET 성별 = '여'
WHERE 이름 = '이장래'

하지만...

아쉽게도 데이터 삭제는 불가능합니다. 만일 다음과 같이 DELETE 문을 수행하게 되면

DELETE EXCEL...sheet1$ WHERE 이름 = '이장래'

다음과 같은 오류가 발생합니다.

서버: 메시지 7345, 수준 16, 상태 1, 줄 1
OLE DB 공급자 'Microsoft.Jet.OLEDB.4.0'이(가) 'sheet1$' 테이블에서 삭제할 수 없습니다. RPC 오류 등과 같이 복구할 수 있는 공급자 오류가 있습니다.
[OLE/DB provider returned message: 이 ISAM에서는 연결된 테이블의 데이터를 삭제할 수 없습니다.]
OLE DB 오류 추적 [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IRowsetChange::DeleteRows returned 0x80040e21: DBROWSTATUS_E_FAIL].

4. QA를 이용한 엑셀 워크시트 연결된 서버 만들기

QA를 이용해서 연결된 서버를 만드는 과정은 다음과 같습니다.

USE master
GO

sp_addlinkedserver N'Excel', N'Jet 4.0',
N'Microsoft.Jet.OLEDB.4.0',
N'c:\temp\연락처.xls', NULL, N'Excel 8.0'
GO

sp_addlinkedsrvlogin N'Excel', false, sa, N'ADMIN', NULL
GO

5. 정리

엑셀 워크시트를 연결된 서버로 사용하는 경우는 그렇게 많지는 않습니다. 하지만 충분히 활용 가능한 부분이 많이 있습니다. 예를 들어 외부에서 데이터를 받아 왔는데 엑셀 워크시트로 되어 있는 경우(우편번호 등) 연결된 서버를 사용하면 됩니다. 물론 DTS를 이용해서 엑셀 워크시트의 내용을 테이블로 올려 사용할 수도 있습니다. 어떤 방법을 사용해도 상관은 없지만, 이런 방법도 있고 저런 방법도 있다는 사실을 기억해 두시면 언젠가는 도움이 될것이라 생각합니다.

출처 : http://www.sqlworld.pe.kr