CODEWITHSUNDEEP
asp.netsql-serverxlsxoledbconnection
Yukesh Kumar
Yukesh Kumar

Reputation: 11

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

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

Upvotes: 1

Views: 7100

Answers (2)

Hajaniaina
Hajaniaina

Reputation: 1

This is a security issue, you should give full access on the folder of the excel template and same for the destination

Upvotes: 0

Gomain Hoyes
Gomain Hoyes

Reputation: 76

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

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

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

Upvotes: 2

Related Questions