데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

엑셀2007 이상을 SQL에서 직접 쿼리하는것이 생각보다 쉽지가 않습니다.

최신 환경일수록 여러 환경적인 특수성을 많이 타서 안되는 경우가 많습니다.

 

저도 테스트하면서 여러가지 돌발변수들이 많이 발생해서 시행착오를 많이 겪었습니다.

심지어 셋팅이 잘못된 상태로 엑셀에 직접 쿼리하면 SQL Server 를 중지시키기도 합니다.

아래 방법은 절대 라이브에서는 실행하지 마시기 바랍니다.

 

개발환경이나 테스트 환경에서만 하시고, 라이브에서는 xls 는 SSIS 를 이용하고 xlsx 는 .txt 나 .csv 등으로

변환해서 안전하게 데이터 가져오기 방법을 사용하시는것을 추천합니다.

 

최신 OS , DBMS 에서 엑셀을 직접쿼리하는 방법 입니다.

 

엑셀 2010 직접읽기 안될 경우 해결책..

환경 : Windows 8 Enterprise K x64 + SQL Server 2012 x64 SP1 + CU3 (11.0.3349) Developer Edition

 

-- Microsoft Access Database Engine 2010 Redistributable Download

http://www.microsoft.com/en-us/download/details.aspx?id=13255

 

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'Ad Hoc Distributed Queries', 1;

GO

RECONFIGURE;

GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.4.0', N'AllowInProcess', 1

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.4.0', N'DynamicParameters', 1

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1

GO

 

반드시 1 줘야 . 그리고 4.0, 12.0 둘다 동일하게 1 줘야함..

0 으로 엑셀에 쿼리하면 SQL Server 서비스가 멈추어 버리기도 !!! 매우매우 조심!!!

xp_readerrorlog 에 아무것도 안남음. 블랙박스 걸어도 아무것도 안남음.

이벤트로그에 Application Error 기록만 남는다.

 

엑셀파일을 드라이브 루트에 넣지 말고 하위폴더에 넣는다. 폴더 위치를 바꾸어 가며 해본다.

한번 되면 시간이 지나면 전에 안되던 폴더도 된다..

 

안될시 윈도우 SQL Service 시작계정의 임시폴더에 everyone 권한 준다.

C:\Users\{SQL Service account name}\AppData\Local\temp

 

ex)

C:\Users\MSSQLSERVER\AppData\Local\Temp

 

쿼리방법:

SELECT *

FROM OPENDATASOURCE

('Microsoft.ACE.OLEDB.12.0',

'Data Source=D:\DBWork\TblMenu.xls;

        Extended Properties=Excel 12.0')...[TblMenu$]

GO

 

-- excel 2010

SELECT *

FROM OPENROWSET

('Microsoft.ACE.OLEDB.12.0',

'Excel 12.0 Xml;HDR=YES;Database=D:\test\TblMenu.xlsx;'

,'SELECT * FROM [TblMenu$]')

GO

 

-- excel 2003

SELECT *

FROM OPENROWSET

('Microsoft.ACE.OLEDB.12.0',

'Excel 12.0 Xml;HDR=YES;Database=D:\test\TblMenu.xls;'

,'SELECT * FROM [TblMenu$]')

GO

 

 

-- 참조사이트

http://www.thorntontechnical.com/tech/databases/the-ole-db-provider-microsoft-ace-oledb-12-0-for-linked-server-null-reported-an-error-access-denied

 

by 유효열 http://blog.naver.com/soundkey

 

No. Subject Author Date Views
2190 MSSQL과 Oracle 함수 비교 3탄 - 9가지 함수(마지막) [5] 쓸만한게없네(윤선식) 2010.11.08 81484
2189 DB 오류검사기록 테이블, 백업할때 오류정보 기록테이블 [3] 차주언 2011.07.20 56462
2188 10_SQL 2008 강좌 - (1) DB관리 - DB 축소(SHRINK) [8] jevida(강성욱) 2011.03.01 50812
2187 Linked Server Open Query 이용 시 USE 명령어 사용하기 [1] 쓸만한게없네(윤선식) 2012.11.07 50664
2186 sa 비밀번호를 잊어먹었을 때 대처방법 [8] 이스트럭(강동운) 2011.06.13 46802
2185 SSMS 줄 번호 표시 jevida(강성욱) 2015.03.26 46530
2184 SMO를 이용한 DB백업 [3] 우주인(김재훈) 2011.03.17 45006
2183 [TIP]MSSQL 와일드 카드를 이용한 검색과 주의점 [16] 쓸만한게없네 2010.10.12 44197
2182 프로파일러 사용법. (완전 초보자 기준!) [10] jevida(강성욱) 2011.04.26 43387
2181 SSMS 단축키. [12] 쓸만한게없네(윤선식) 2011.02.07 43100
2180 SQL Server 로긴 유저 및 각 DB별 사용자 권한 조회 [1] 문연군 2010.12.20 42279
2179 MSSQL과 Oracle 함수 비교 2탄 - 9가지 함수 [10] 쓸만한게없네(윤선식) 2010.10.27 41883
» Microsoft.ACE.OLEDB.12.0 을 이용한 엑셀 2010 직접쿼리하기 [3] 열이 2013.03.20 41523
2177 DBCC TRACE ... [4] 이스트럭(강동운) 2012.04.13 40939
2176 SQL Server 와 MySQL 문자열 함수 비교 10 가지 [5] 이호엽 2010.12.29 38229
2175 SQL2012는 SSMS의 시작 옵션(Startup option) 설정 위치가 바뀌었어요. [1] 코난(김대우) 2012.01.13 37459
2174 MSSQL과 Oracle 함수 비교 - 첫번째 10가지 함수. [7] 쓸만한게없네(윤선식) 2010.10.25 35823
2173 CONVERT 스타일 별 결과 값 [3] Alucard(강산아) 2010.10.14 35497
2172 트랜잭션 열린넘 있으면 로그백업할때 사이즈가 커진다!! [5] 차주언 2009.12.02 33535
2171 초간단 - 알만한 사람은 다 아는 읽고, 쓰고, 실행하는 권한만 주기 스크립트. [2] 쓸만한게없네(윤선식) 2011.07.21 32939





XE Login