SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Linked Server - Excel Spreadsheet		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

j_m
Starting Member

3 Posts
Posted - 05/12/2008 :  13:20:31  Show Profile  Reply with Quote
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

United Kingdom
3560 Posts
Posted - 05/13/2008 :  01:53:18  Show Profile  Reply with Quote
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
Go to Top of Page

j_m
Starting Member

3 Posts
Posted - 05/13/2008 :  06:57:11  Show Profile  Reply with Quote
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)".
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

United Kingdom
3560 Posts
Posted - 05/13/2008 :  07:26:24  Show Profile  Reply with Quote
Change the N:\ to your network drive as I would assume the N:\ is not a drive on your SQL box.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000