MS SQL 엑셀 업로드 문제 (해결 중)

SoYoung Kim
Aug 23, 2017 · 4 min read

SELECT a.*

FROM OPENROWSET (‘Microsoft.Jet.OLEDB.12.0’,

’Excel 12.0;Database=\\sharedisk:\Data\data1.xlsm;HDR=YES’,

‘SELECT * FROM [Sheet1$A10:ZZ]’)

GO

특정 계정에서 안되서 여러가지로 시도해보고 있다.

Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)”.

  1. 현재 linked server가 뭐가 있나 보면 뭔가 있다.

exec sp_linkedservers (확인 방법)

2. 파일이 이미 열려있는 것은 아닌가, 경로명이 이상한가 하여 새로운 폴더에 옮겨놓고 시도해보았다.(X)

3. 혹시 HDR=Yes 가 마음에 안드나..Admin으로 (X)

4. 마지막으로 SQL Server TEMP 디렉토리에 권한을 주라는 말이 나왔는데...

참고할만 하므로 아래 첨부

5. 결국 disk 인식 문제였는데, 일반 User가 일반적인 C:, D:, E:… 접근은 잘 되는데, share disk의 경우 인식 못하고 파일을 못 여는 문제

  • 할 수 없이 일단 일반disk에 올리고 테스트
  • share disk를 어떻게 인식 시킬 수 있는지는 고민..

6. 지인 통해 문의해서 받은 답변은 아래와 같으나 해결이 된 것은 아니었다.

USE [master]

GO

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

GO

7. 이쯤 되면 내 프로그램만의 문제인가 하는 의구심이 든다. 다른 사람들 프로그램을 실행시켜봤는데, 아주 잘됐다. 새 프로그램 만들어서 해보자고 하고 같이 했는데, 역시 안됐다. 새로 프로그램을 구성할 때 문제일까?

>>>>> 참조 (http://m.dbguide.net/qna.db?cmd=view&boardUid=189008&boardConfigUid=30&boardStep=0&categoryUid=206)

>>>>> 참조 (https://www.excel-sql-server.com/excel-import-to-sql-server-using-distributed-queries.htm)

>>>>>참조 (출처: http://121202.tistory.com/23 [책방 창고])

>>>>>참조 (https://www.excel-sql-server.com/excel-import-to-sql-server-using-linked-servers.htm)

아래와 같은 오류가 발생한다면, 서버상에 해당 공급자를 등록해 주어야 합니다.

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” reported an error. Access denied.

해결방법

USE [master]

GO

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

GO

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

GO

혹시 아래와 같은 오류가 발생한다면 다음과 같이 해결할 수 있습니다.

(가급적 업로드 작업은 데이터베이스 서버 상에서 처리하는 것이 바람직하겠습니다)

Msg 15281, Level 16, State 1, Line 1

SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries’, see “Surface Area Configuration” in SQL Server Books Online.

[해결방안]

EXEC sp_configure ‘show advanced options’, 1

RECONFIGURE WITH OVERRIDE

EXEC sp_configure ‘Ad Hoc Distributed Queries’, 1

RECONFIGURE WITH OVERRIDE

Grant rights to TEMP directory

This step is required only for 32-bit SQL Server with any OLE DB provider.

The main problem is that an OLE DB provider creates a temporary file during the query in the SQL Server temp directory using credentials of a user who run the query.

The default directory for SQL Server is a default directory for SQL Server service account.

If SQL Server is run under Network Service account the temp directory is like:

C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp

If SQL Server is run under Local Service account the temp directory is like:

C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp

Microsoft recommends two ways for the solution:

  1. A change of SQL Server TEMP directory and a grant of full rights for all users to this directory.
  2. Grant of read/write rights to the current SQL Server TEMP directory.

See details: PRB: “Unspecified error” Error 7399 Using OPENROWSET Against Jet Database

Usually, only a few accounts are used for import operations. So we can just add rights for these accounts.

For example, icacls utility can be used for the rights setup:

icacls C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp /grant vs:(R,W)

if SQL Server is started under Network Service and login “vs” is used to run the queries.

The SQL Server Error Message if a user has no rights for SQL Server TEMP directory

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

>>> 참조

[MSSQL] sp_addlinkedserver 프로시저. 외부 서버로 연결

출처: http://121202.tistory.com/23 [책방 창고]

  • 서버 등록

sp_addlinkedserver ‘servername’ , ‘ ‘, ‘SQLOLEDB’, ‘IP’, ‘’, ‘DB이름’

@server = ‘ name ‘

서버 이름, 기본값 없음.

@srcproduct = ‘ ‘

OLEDB 원본 제품 이름, default NULL

@provider = ‘SQLOLEDB’

데이터 원본에 해당하는 OLE DB 공급자의 고유 PROG ID ( Programming ID)

@datasrc = ‘192.168.2.1’

OLE 공급자가 해석하는 데이터 원본이름

원격서버의 IP , 연결할 서버의 아이피

@prastr = ‘ ‘

OLEDB 공급자 연결 문자열, default NULL

@catalog = ‘DATABASE이름’

OLEDB 공급자에 연결할때 사용되는 catalog, database name

  • 서버 등록 확인

select * from master.dbo.sysservers

  • 연결계정등록

sp_addlinkedsrvlogin ‘Server Name’, ‘false’, NULL, ‘loginID’, ‘passwd’

sp_addlinkedsrvlogin

@rmtsrvname = ‘Server Name’

Linked server Name, 기본값 없음

@useself = ‘True’ or ‘false’

Login Id 사용 유무 , default True

@locallogin = NULL

local server login 여부 , default NULL

@rmtuser = ‘loginID’

login ID , 사용자 이름

@rmtpasswd = ‘passwd’

Login Passwd

  • 연결계정 삭제

sp_droplinkedsrvlogin ‘ServerName’ , ‘ID’

@rmtsrvname = ‘ServerName’

login linked server name, not default value

@locallogin = ‘ID’

삭제할 Login name, 기본값 x

  • 서버 삭제

sp_dropserver ‘ServerName’, ‘droplogins’

@server = ‘ServerName’

삭제할 linkedserver name , 기본값 없음

@droplogins = ‘droplogins’ | NULL

지정된경우 관련된 원격서버 로그인 들도 제거해야 함을 나타냄 , 기본값 NULL

출처: http://121202.tistory.com/23 [책방 창고]

)
Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade