Openrowset function failure

Question

I'm executing this openrowset function:

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
    'Excel 12.0;Database=C:\Users\JCPABALAN\Desktop\Data Migration\ListOfDiscards.xlsx;HDR=YES', 
    'SELECT * FROM [Sheet1$]')

But it gave me the following error

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "The Microsoft Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly. If 'Sheet1$' is not a local object, check your network connection or contact the server administrator.".
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

I have already set the Ad Hoc Distributed Queries into 1 and I Installed Microsoft ACE and also executed this line of code:

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

But it still won't work.

Answer 1

You Error message has two parts :

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "The Microsoft Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly. If 'Sheet1$' is not a local object, check your network connection or contact the server administrator.".

And

Msg 7350, Level 16, State 2, Line 1 Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

That the second part is because of error of first part, So your main error is at first part, that tells us:

The Microsoft Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly. If 'Sheet1$' is not a local object, check your network connection or contact the server administrator.

Some common causes and solutions are these:

• Path not exists : File or Path C:\Users\JCPABALAN\Desktop\Data Migration\ListOfDiscards.xlsx is not exist;

  Note : SQL Server C:\ is referring to the \\Server\C$, So if you are using SSMS and you register a Server and are connecting to it, your path is not exist.

• File permission is denied : File or Path C:\Users\JCPABALAN\Desktop\Data Migration\ListOfDiscards.xlsx have security level that you can access them from SQL Server service account, You can grant access to SQL Server service account.

• Sheet Name is invalid : Worksheet Sheet1 is not a valid sheet name in your workbook sheets, You maybe change its name.