Cannot use Linked Server to Excel

Question

Cannot use Linked Server to Excel

rschaeferhig

SSCrazy Eights

Points: 8776
More actions
April 28, 2004 at 4:55 am

#65131

I tried connecting to an Excel Spreadsheet using sp_AddLinkedServer as:
EXEC sp_addlinkedserver 'ExcelSource',
   'Jet 4.0',
   'Microsoft.Jet.OLEDB.4.0',
   'C:\Documents and Settings\RS02130\Desktop\OLS Requests.xls',
   NULL,
   'Excel 5.0'
GO
I got a couple of "1 rows affected" messages, but no errors. I then tried to select from a named range ("OLS") as:
Select * from ExcelSource...OLS
and received:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: Unspecified error]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005:   ].
Anyone have any ideas what's causing this? As far as I know I have the latest everything (including Jet) installed.
Also, how would I specify an entire worksheet (i.e. Sheet1) in the select if I had no named range?

Viewing 10 posts - 1 through 9 (of 9 total)

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

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

I think (don't remember exactly) that you are missing the 2nd step which is to assign the permissions to the newly linked server. Here is syntax to check

EXEC sp_addlinkedserver 'ExcelSource',

'Jet 4.0',

'Microsoft.Jet.OLEDB.4.0',

''C:\Documents and Settings\RS02130\Desktop\OLS Requests.xls',

NULL,

'Excel 8.0'

EXEC sp_addlinkedsrvlogin 'ExcelSource', false, sa, 'admin', NULL

SELECT * FROM ExcelSource...Sheet1$

Good Luck

Good Hunting!

AJ Ahrens

webmaster@kritter.net

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 2

Can anyone explain why I would have to have a login for an Excel Spreadsheet? At best I would think "False, Null, Null, Null" would work. Excel doesn't do any kind of authentication, unless the userid being passed from SQL has to authenticate against NTFS to access the file in which case it would have to be a domain login.

I'm confused....

wz700 SSCertifiable Points: 7133 More actions · Answer 3

Agree with you.

Have you tried "False, Null, Null, Null", it may well be the case.

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 4

Tried it:

EXEC sp_addlinkedserver 'ExcelSource',

'Jet 4.0',

'Microsoft.Jet.OLEDB.4.0',

'C:\Documents and Settings\RS02130\Desktop\OLS Requests.xls',

NULL,

'Excel 8.0'

GO

EXEC sp_addlinkedsrvlogin 'ExcelSource', False, null, null, null

GO

select * from ExcelSource...OLS

Result is:

Server: Msg 7399, Level 16, State 1, Line 1

OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.

[OLE/DB provider returned message: Unspecified error]

OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 5

Have you tried the syntax from my last post??

EXEC sp_addlinkedsrvlogin 'ExcelSource', false, sa, 'admin', NULL

Does the above work ??

Good Hunting!

AJ Ahrens

webmaster@kritter.net

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 6

I'd love to, but I don't have SA access to the SQL Server. I'm a SQL developer. The SQL SA's are in a different division.

Besides, what good is the whole concept of Linked Servers, particularly with respect to Excel spreadsheets, if only an SA can use it?

PurpleLady SSCertifiable Points: 7819 More actions · Answer 7

Check out - I use it and it works well.

http://www.sqlservercentral.com/scripts/contributions/763.asp

wz700 SSCertifiable Points: 7133 More actions · Answer 8

False, null, null, null works for me.

If worksheet is OLS, would it be

select * from ExcelSource...OLS$

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 9

"OLS" is a named range so I think I specified it correctly. I was wondering how to just specify a worksheet name.

I found the following KB references to my error message. They may apply so I've sent them on to the SQL Server team.

http://support.microsoft.com/default.aspx?scid=kb;en-us;818182

http://support.microsoft.com/default.aspx?scid=kb;en-us;814398

I'll post the results.