sql server - Importing Sharepoint Excel Files using OPENROWSET

Question

I'm trying to import an Excel (xlsx) file into our SQL server 2012, from our MS Sharepoint site, using the following OPENROWSET statement:

SELECT * FROM OPENROWSET (          
            'Microsoft.ACE.OLEDB.12.0',
            'Excel 12.0;Database=\MycompanyName.Sharepoint.comdirectoryfile name.xlsx',
            'SELECT * FROM [Sheet Name$]'
)

Returns the (generic) error:

 Msg 7399, Level 16, State 1, Line 1
 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The              provider did not give any information about the error.
 Msg 7303, Level 16, State 1, Line 1
 Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked    server "(null)".

I'm assuming the syntax is correct, and is failing due to a Sharepoint access/permissions issue. The SQL service runs under the Local System account:

Therefore, I presume Sharepoint rejects the request since it thinks the request is coming from some local system account, and not me ([email protected]).

I changed the SQL Service "log on as" using my own credentials, for which it accepted, but still threw the same error described above when I tried re-running the OPENROWSET statement.

Another thing to add, I'm also the admin of the sharepoint site. It only lets me add users with valid email addresses. If I were to create a domain service account, not sure if I can even add it the sharepoint member list.

Any help?!

Answer 1

等待大神答复