This is most well known error when we are trying to access excel from SQL Server. To fix this issue, we need to follow below steps.
- Install Microsoft Access Database Engine 2010 Redistributable based on 32 bit or 64 bit version of Windows from below link.
http://www.microsoft.com/en-in/download/details.aspx?id=13255 - Registry changes. Navigate to <HKLM>\Software\Microsoft\Microsoft SQL Server\Providers\Microsoft.Jet.OLEDB.4.0 and Create a DWORD named DisallowAdhocAccess with the value 0. Just to check if you are in the right place there will be another DWORD entry under the name of AllowInProcess, which should be set to 1 already.
- Restart agent and server services
- Enable OLEDB Driver in SQL Server
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
I followed Your instruction but now when I execute the script with the OpenRowSet function, it shows Followinf Error message
ReplyDeleteMsg 7314, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" does not contain the table "Sheet1$". The table either does not exist or the current user does not have permissions on that table.
Please check below things to find the root error.
Delete1) Check if the sheet name is "Sheet1".
2) Check if the file exist at the location.
3) If it's UNC path, please check if you have enough permission.
Thanks!! I got mine working without going to REGEDIT.
ReplyDelete