[MSSql] Excel 읽기, 불러오기

반응형

간혹 MS-SQL Server 에서 Excel 로 된 데이터파일을 읽어 오고 싶을때
아래와 같이 실행하면 다음과 같은 오류가 발생한다.

INSERT INTO KeyMapInit
SELECT nFunctionNo, GETDATE(), NULL, nExtendedKey, nASCIICode, sDescription

FROM OpenRowset(

'Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=d:\KeyMapInit.xls',

'SELECT * FROM `KeyMapInit$`') AS T1

메시지 15281, 수준 16, 상태 1, 줄 1
구성 요소 'Ad Hoc Distributed Queries'이(가) SQL Server 보안 구성의 일부로 해제되었으므로 이 구성 요소의 문 'OpenRowset/OpenDatasource'에 대한 액세스가 차단되었습니다.
시스템 관리자는 sp_configure를 사용하여 'Ad Hoc Distributed Queries'의 사용을 활성화할 수 있습니다.
'Ad Hoc Distributed Queries' 활성화 방법은 SQL Server 온라인 설명서의 "노출 영역 구성"을 참조하십시오.

이럴때는 다음과 같이 sysconfigure 설정을 확인한 후

SELECT * FROM sys.configurations  WHERE name = Ad Hoc Distributed Queries'

위의 value 값이 0으로 되어 있으면 이것을 1로 설정하면 사용이 가능하다.
설정 방법은 다음과 같다.

EXEC sp_configure 'Ad Hoc Distributed Queries', '1'

go

RECONFIGURE WITH OVERRIDE

go

주의 1)
Excel 시트에서 SELECT * FROM `KeyMapInit$` 에서 "`"(키보드 숫자 1번 옆에있는 문자)요 문자에 주의하자.
이게 없을경우 다음과 같이 구문 오류가 난다. -_-;;;

연결된 서버 "(null)"의 OLE DB 공급자 "Microsoft.Jet.OLEDB.4.0"이(가) 메시지 "FROM 절의 구문 오류입니다."을(를) 반환했습니다.
메시지 7321, 수준 16, 상태 2, 줄 1
연결된 서버 "(null)"의 OLE DB 공급자 "Microsoft.Jet.OLEDB.4.0"에 대해 실행할 쿼리 "SELECT * FROM KeyMapInit$"을(를) 준비하는 동안 오류가 발생했습니다.

주의 2)
위와 같이 설정을 해도 다음과 같은 오류가 나올 수 있다. 아직 이것에 대한 해결 방법은 찾지 못했다.

메시지 7308, 수준 16, 상태 1, 줄 1
OLE DB 공급자 'Microsoft.Jet.OLEDB.4.0'은(는) 단일 스레드 아파트 모드에서 실행되도록 구성되어 있으므로 분산 쿼리에 사용할 수 없습니다.

STA(단일 스레드 아파트 모드???) 와 MTA(멀티 스레드 아파트 모드???)의 차이점이라고 하는데 Windows 7 64비트 + MS-SQL Server 208 Development 버전에서는 위와 같은 오류가 발생했다. Windows Server 2003 32비트 + MS-SQL Server 208 Development 버전에서는 정상적으로 실행되었다.

출처: https://devdb.tistory.com/18 [DB 관련:티스토리]