OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server (null)

Question

I am trying to upload .xlsx into SQL database table using OLE DB provider "Microsoft.ACE.OLEDB.12.0" but it throws the following Error:

Error
'Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)". OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Cannot update. Database or object is read-only.".'

my sql code is:

'ALTER PROCEDURE [dbo].[spx_ImportFromExcel07]
   @SheetName varchar(20),
   @FilePath varchar(100),
   @HDR varchar(3),
   @TableName varchar(50)
AS
BEGIN
    DECLARE @SQL nvarchar(1000)

    IF OBJECT_ID (@TableName,'U') IS NOT NULL
   SET @SQL = 'INSERT INTO ' + @TableName + ' SELECT [Dealer Number],[Dealer Name],[Policy Number],[Policy Status],[Processing Date (ISO)],
                [Policy Start Date (ISO)],[Request Cancel/ Amend Date],[Amend/Cancel Type],[Cancel/Amend Text],[Chassis Number],[Registration Number],
               [Manufacturer Desc],[Model Desc],[Transmission Desc],[Fuel Type Desc],[Registration Date (ISO) L],[Product Description],
                [User Id],[Customer Full Name],[Sub Total],[Tax],[Customer Charge],null,null,null,null FROM OPENDATASOURCE'
    ELSE
      SET @SQL = 'SELECT * INTO ' + @TableName + ' FROM OPENDATASOURCE'

    SET @SQL = @SQL + '(''Microsoft.ACE.OLEDB.12.0'',''Data Source='
    SET @SQL = @SQL + @FilePath + ';Extended Properties=''''Excel 12.0;HDR='
    SET @SQL = @SQL + @HDR + ''''''')...['
    SET @SQL = @SQL + @SheetName + ']'
    EXEC sp_executesql @SQL
END'

after try this steps http://www.excel-sql-server.com/

Error is

'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.'

Have you checked the "bitness" of your solution. You may have to install the 32 bit version of ACE. — cameront
There is a very good article which explains all of the various installation steps and configurations required here- excel-sql-server.com/… — cameront

Gomain Hoyes Gomain Hoyes · Accepted Answer · 2017-03-14T11:46:36

Check (Windows) file access permissions on the .xlsx file.

It is very likely that the (Windows) user that created the file is different from the (Windows) user that the OLEDB driver is using to access the file. This is usually the case when you have write access when logged in to sql sever using Windows Authentication but not when using SQL Server Authentication.

Go to the file in windows explorer and do the following

Right click and select properties.
In the Security tab, click Edit...
In Group or user name: select the group Users(<<your-computer-name>>\Users)
In Permissions for Users check to Allow Write
Click OK to save changes.

This will allow any user to have write access to the file, thus enabling insert into linked server tables.

OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server (null)

2 Answers