MS SQL 엑셀 업로드 문제 (해결 중)
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)”.
- 현재 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:
- A change of SQL Server TEMP directory and a grant of full rights for all users to this directory.
- 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 [책방 창고]