Please help, I'm getting desperate. Any ideas warmly welcomed!
I'm trying to read from a basic excel file (1000 or so rows from
column A) but am having problems. The code I am using is:
Declare @.Return Int
SET NOCOUNT ON
Exec @.Return= [master]..[sp_addlinkedServer] 'READ_XLS', 'EXCEL',
'Microsoft.Jet.OleDB.4.0' , 'e:\jsbackup\RACodes.xls',
NULL, 'EXCEL 8.0'
print 'set up Return : ' + convert(varchar(10),@.Return)
--NB E: is the drive as seen oon the server
EXEC sp_addlinkedsrvlogin
@.rmtsrvname = 'READ_XLS',
@.useself = 'true'
print 'login Return : ' + convert(varchar(10),@.Return)
When I try to read from the (one) excel sheet in the file, via
Select * from [READ_XLS]...RACodes$
or to list what tables/sheets are available, via
exec sp_tables_ex 'READ_XLS'
I get the following error:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
Authentication failed.
[OLE/DB provider returned message: Cannot start your application. The
workgroup information file is missing or opened exclusively by another
user.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80040e4d: Authentication
failed.].
What am I missing?
*Many* thanks in advance.
AndyA few things to check:
Do you have the .xls file already open?
Is e:\jsbackup\RACodes.xls a server path?
I recall that you have to set named ranges for it to appear as tables
in Excel.
I don't know whether it helps but I have written an addin for Excel
that can push the data out from Excel (you are trying to pull it in
from the database). If you can reconsider your design and push it from
Excel you can perhaps use it.
SQL*XL is an addin for Excel that let you manage your database from
Excel. It is an end-user tool but it also lets you record all actions
as a macro (VBA) and therefore you can code/script applications with
it.
Have a look at SQL*XL at www.oraxcel.com
Best regards, Gerrit-Jan Linker
Linker IT Consulting Limited
www.oraxcel.com
