데이터베이스 개발자 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
1533 SSIS - 검사점 jevida(강성욱) 2013.04.19 8352
1532 SSIS - 패키지 구성 [1] jevida(강성욱) 2013.04.19 10120
1531 SSIS - 프로세스 실행 태스크 [2] jevida(강성욱) 2013.04.19 10350
1530 SSIS - 변수 – 다양한 사용자 변수를 만들어 보자 [3] jevida(강성욱) 2013.04.19 10802
1529 SQL Server Version별 Sample Database Download 경로 [2] 쓸만한게없네(윤선식) 2013.04.17 6810
1528 InstrCount함수와 InstrCountRev함수를 한번 만들어봤습니다. [5] Light 2013.03.27 6094
1527 구분자에 의해 구분되어 반환되는 split함수가 없어서 한번 만들어 봤습니다. [2] Light 2013.03.27 7537
» Microsoft.ACE.OLEDB.12.0 을 이용한 엑셀 2010 직접쿼리하기 [3] 열이 2013.03.20 42137
1525 Online restore [1] jevida(강성욱) 2013.03.15 6308
1524 SQL Server User Connections [1] jevida(강성욱) 2013.03.15 10597
1523 스크립트 구성 요소 – [데이터 대상] 사용 [1] jevida(강성욱) 2013.03.13 6763
1522 스크립트 구성 요소 – [데이터 변환] 사용 jevida(강성욱) 2013.03.13 6128
1521 SSIS - 스크립트 구성 요소 – [데이터 원본] 사용 jevida(강성욱) 2013.03.12 7832
1520 SSIS - 피벗 해제 변환 - 피봇된 데이터를 테이블 형태로 변환 하자! jevida(강성욱) 2013.03.12 7199
1519 SQL Server 단일 사용자 설정 시 SSMS로 로그인하기 [1] 쓸만한게없네(윤선식) 2013.02.25 12284
1518 SELECT 가 Deadlock? [2] 이스트럭(강동운) 2013.02.16 11693
1517 한글 초성 관련 [4] 건우아빠 2012.12.26 13133
1516 .NET 4.5 (VS2012) 설치 후 SSMS 에서 원격 MSSQL 2008 등이 접속 안될 경우! [2] 컴포지트 2012.12.11 17398
1515 SQL프로시저들의 매개변수를 한번에 샥 보여줍니다 [3] 차주언 2012.12.10 9268
1514 SSIS - 피봇 변환 - 데이터를 행렬 변환 하자! jevida(강성욱) 2012.11.30 10004





XE Login