1

I'm trying to insert a resultset into a linked server pointing to an Excel file.

Here is my script:

exec sp_addlinkedserver 
@server = N'ExcelTEST',
@srvproduct = N'Excel 12.0',
@provider = N'Microsoft.ACE.OLEDB.12.0',
@datasrc = N'\\MyServer\MyPath\MyFile.xlsx',
@provstr = N'Excel 12.0;HDR=No,IMEX=1'                    
 --
exec sp_addlinkedsrvlogin
    @rmtsrvname = N'ExcelTEST',
    @useself = N'False',
    @locallogin = NULL,
    @rmtuser = NULL,
    @rmtpassword = NULL 
GO
--
INSERT INTO ExcelTEST...[Sheet2$] 
SELECT TOP 10 *
FROM dbo.myTable
GO

sp_dropserver N'ExcelTEST', 'droplogins'
GO

I then get the following error:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "ExcelTEST" returned message "Cannot update. Database or object is read-only.".

Msg 7399, Level 16, State 1, Line 6 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "ExcelTEST" reported an error. The provider indicates that the user did not have the permission to perform the operation.

Msg 7343, Level 16, State 2, Line 6 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "ExcelTEST" could not INSERT INTO table "[ExcelTEST]...[Sheet2$]".

How can I make the connection to the Excel file read-write?

Willi Mentzel
  • 27,862
  • 20
  • 113
  • 121
user1238918
  • 272
  • 2
  • 7
  • 17
  • [Have you considered just using the Export Wizard for this instead?](http://stackoverflow.com/a/87772/643591) – Michael Fredrickson Mar 05 '13 at 19:20
  • what error on first exec sp_addlinkedserver...? – Aleksandr Fedorenko Mar 05 '13 at 20:00
  • Export Wizard won't work as I'm hoping to use the query in SSIS to automate the data pull. The linked server is showing it is read-only, it would need to be changed to read-write which is where the issue is. However, we may have found a better solution than using a linked server due to the sheer number of Excel files that need to be loaded and our service account won't have permission to create/drop linked servers. – user1238918 Mar 06 '13 at 21:57

0 Answers0