Prev: ounces vs. pounds
Next: watermark
From: default_user on 21 May 2010 16:47
Greetings!

I am having difficulty updating an Excel worksheet via the ACE.OLEDB.12.0
provider.

I have this worksheet defined as a linked server in SQL Server via this
provider, and all attempts to update the lone worksheet in this file as a
linked server results in the following:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "linked_excel"
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 "linked_excel".

The query:
update linked_excel...sheet1$ set error_col='hithere' where [code])='G'

However, when I try to perform precisely the same update against the same
source via openrowset, it works, to-wit:

update openrowset('Microsoft.ACE.OLEDB.12.0','Excel
12.0;HDR=yes;Database=f:\path_to_file\filename.xlsx','select * from
[sheet1$]')
set error_col='hithere'
where [code]='G'

SELECT's performed against either version work properly.

The linked server behavior is consistent across SQL 2005 and 2008
installations. The OLEDB errors in the trace indicate a NotImplemented error
duing call to QueryInterface, but I have not been able to dig deeper than
that bit of information.

I would greatly appreciate any tips or help.

Many thanks,
-David

From: David on 25 May 2010 11:25
I would like to add some additional information on this problem.

As noted, Excel sheets created as linked servers from SQL Server via the ACE
OLEDB provider version 12 fail on table updates. Inserts and selects work.

I have found an odd twist to this.

I have prepared a simple console .NET 3.5 application that uses the same
OLEDB provider, and performs the identical update against the same sheet of
the same Excel 2007 spreadsheet, and the update statement works perfectly.

This would tend to suggest that there is a SQLServer-specific problem with
the way it is dealing with this linked server against this provider, but most
questions that involve the ACE provider are routed here (to Office-specific
forums). I can reproduce this error on SQL Server 2005 and 2008 boxes.

I have tried DBCC traceon(7300) to gain more information on the error, but
no additional information is provided. I have already added the
AllowInProcess and DynamicParameters registry entries as noted in other posts
describing similar behavior, but the presence/absence of these entries makes
no difference.

If anyone has any thoughts on this, I'd be most appreciative. I honestly
think we're dealing with a bug in SQL Server, because bypassing SQLServer
removes the problem - but that's not eliminating the possibility of a bug in
the way the ACE provider is interacting with it.

Help greatly appreciated.

-David
"default_user" wrote:

> Greetings!
>
> I am having difficulty updating an Excel worksheet via the ACE.OLEDB.12.0
> provider.
>
> I have this worksheet defined as a linked server in SQL Server via this
> provider, and all attempts to update the lone worksheet in this file as a
> linked server results in the following:
>
> OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "linked_excel"
> 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 "linked_excel".
>
> The query:
> update linked_excel...sheet1$ set error_col='hithere' where [code])='G'
>
> However, when I try to perform precisely the same update against the same
> source via openrowset, it works, to-wit:
>
> update openrowset('Microsoft.ACE.OLEDB.12.0','Excel
> 12.0;HDR=yes;Database=f:\path_to_file\filename.xlsx','select * from
> [sheet1$]')
> set error_col='hithere'
> where [code]='G'
>
> SELECT's performed against either version work properly.
>
> The linked server behavior is consistent across SQL 2005 and 2008
> installations. The OLEDB errors in the trace indicate a NotImplemented error
> duing call to QueryInterface, but I have not been able to dig deeper than
> that bit of information.
>
> I would greatly appreciate any tips or help.
>
> Many thanks,
> -David
>
 | 
Pages: 1
Prev: ounces vs. pounds
Next: watermark