Help, unable to update linked Excel File

Question

Help, unable to update linked Excel File

Sam-349201

SSC-Addicted

Points: 435
More actions
September 29, 2010 at 2:10 pm

#97592

Hi,
Running SQL 2008 x64 SP1
I added a linked server, an excel file using OLE DB 12.0
I can read my excel file using :
select * from BOOK1...[sheet1$]
I can also create some new lines ok:
insert into BOOK1...[sheet1$] select '1','2','3'
However I cannot update any record
update BOOK1...[sheet1$] set [col2] = '5' where [col1] = '1'
Here's the error msg: OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "BOOK1" returned message "Bookmark is invalid.".
Msg 7346, Level 16, State 2, Line 1
Cannot get the data of the row from the OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "BOOK1".
Any Idea ? Thanks !

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

How the heck were you able to create the linked server in the first place??? I've been shut out on my sql 2008 standard 64 bit for months trying to do that?

Sam-349201 SSC-Addicted Points: 435 More actions · Answer 2

Instructions are on my blogs : [/url]

BTW I finally found it.

You don't even need a linked server, just use openrowset:

example :

update OPENROWSET('Microsoft.Ace.OLEDB.12.0',

'Excel 8.0;DATABASE=c:\book1.xls', 'Select * from [Sheet1$]')

set col1 = 'Finally working'

where col3 = 'test123'

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Believe me I've googled a tone on that one.

Here's what I have working on my local, 32 bit server :

Select * from openrowset('Microsoft.ACE.OLEDB.12.0',

'Excel 8.0;Database=C:\TireGuideImport\tgpro_oe_1009_excel.xls;HDR=YES',

'select * from [tgpro_oe_1009_excel$]')

When I move that on the 64 bit server I always get :

Msg 7399, Level 16, State 1, Line 3

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.

Msg 7330, Level 16, State 2, Line 3

Cannot fetch a row from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Now here's the really fu**ed up part. If I run the statement from my own dev machine, but connected on the prod server it works. But if I try to run it straight on the 64 bit server I ge the previous error.

I've installed the latest 64 bit ace driver.

I've added the reg keys

I've granted permissions to the file (everyone full control, and even all the server's accounts)

I tried moving the file to the data folder... in which the server is able to read/write

Sam-349201 SSC-Addicted Points: 435 More actions · Answer 4

this link should help you out.

it's a step by step process.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

... the magic reboot didn't work.

I've even removed the file from my own dev machine to make sure I was accessing the prod file.

So from ssms on dev, I can openrowset the local file on prod using ace 12.

But from prod, to open the same file using the same query I get an error!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Sam-349201 (9/29/2010)
this link should help you out.
it's a step by step process.

No dice, I've already done all those things.

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 7

I realize this post is almost a half decade old but I did come across it while searching for an answer to the very same problem and figure others might, as well.

The real problem is that the driver is broken, Microsoft has been able to duplicate the fault, admits it needs to be fixed, closed the CONNECT item promising to not let it slip through the cracks, and it's still broken almost 5 years later. Here's the link.

https://connect.microsoft.com/SQLServer/feedback/details/626965/cannot-update-excel-2007-spreadsheet-as-linked-server-within-sql-2005-or-sql-2008-via-oledb-12-0-provider

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)