• Register
3,890 points
11 5 1
Welcome to Kodlogs, programming questions and answer website.

There are many situations when you have to link your server to Microsoft Excel and read the data from there. While doing that the users can encounter an error and In this article, we are going to discuss how we can fix that error.

Query to read data:

The below query can be used to read data from excel in a shared location.

SELECT * FROM OPENROWSET(

'Microsoft.ACE.OLEDB.12.0'

,'Excel 12.0;Database=\\\\FileServer\\ExcelShare\\HRMSDATA.xlsx;HDR=YES;IMEX=1'

,'SELECT * FROM [EMPMASTER$]')

This will raise an error like;

OLE DB provider ‘Microsoft.ACE.OLEDB.12.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.ACE.OLEDB.12.0’ for linked server ‘(null)’.

Reason 1:

The user can receive an error if the Excel file is currently opened.

Solution:

Close the Excel file that is currently opened and try to execute it again.

Reason 2:

The user can receive this error if Ad Distributed Queries are not enabled by the system administrator.

Solution:

You can use sp_configure file to enable this component. Although this is an advanced option. You will also need to enable “Show Advanced Options” to enable this component.

3,890 points
11 5 1