Linked Server - Excel Spreadsheet

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Linked Server - Excel Spreadsheet

Author

Topic

j_m
Starting Member

3 Posts

Posted - 2008-05-12 : 13:20:31

I am trying to set up a linked server in SQL Server 2005 to link to an excel spreadsheet.

-I am selecting Jet 4.0 as the provider
-Product name is Excel
-Data Source is the path on our network to the excel file: N:\Devon\05403\04.0 Engineering\4.01 Process\Linelist\IFC\LDT Field.xls
-Provider String is Excel 8.0
-Security | Login not defined is set to Be made using the login's current security context.

The Excel file is an Excel 2003 spreadsheet. The worksheet is titled Pages

I have a query window open in SQL Server Management Studio and the following is my select statement:

SELECT * FROM DEVON_LINE_LIST...Pages$

I get the following error message:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "DEVON_LINE_LIST" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "DEVON_LINE_LIST" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "DEVON_LINE_LIST".

I get similar error messages no matter which security settings I pick.

Any thought as to what I can try to get this to work?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-05-13 : 01:53:18

Is this a shared worksheet? If so and it is open, then you will get this error.

Can you run the following?


select 
*
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="N:\Devon\05403\04.0 Engineering\4.01 Process\Linelist\IFC\LDT Field.xls";Extended properties=Excel 8.0')...[Pages$] a

j_m
Starting Member

3 Posts

Posted - 2008-05-13 : 06:57:11

The worksheet is on a shared network drive, but it is not open at the time of executing the query.

When I try to run the query you suggested, I get the following:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-05-13 : 07:26:24

Change the N:\ to your network drive as I would assume the N:\ is not a drive on your SQL box.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources