Creating an Excel linked server

Hello, I am trying to create a linked server to an Excel file (SQL 2005). I
executed the following:
EXEC sp_addlinkedserver test2,
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'd:\test1.xls',
NULL,
'Excel 5.0'
EXEC sp_addlinkedsrvlogin test2, false, rhofing, null
Then I try to select: select * from test2...test
and I get the following error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "test2"
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 "test2".
Can anyone tell me what I am doing wrong? Thanks!Hi Ric,
Place the Excel file on a location where the SQL Server service account has
access to. Also, remove the sp_addlinkedsrvlogin statement. Try this just fo
r
testing:
1) Move the Excel file to a location where SQL Server has access to, maybe,
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
2) Run the same sp_addlinkedserver command
3) Run your select statement. By the way, do you have a range named 'test'
in your Excel file?
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Ric" wrote:

> Hello, I am trying to create a linked server to an Excel file (SQL 2005).
I
> executed the following:
> EXEC sp_addlinkedserver test2,
> 'Jet 4.0',
> 'Microsoft.Jet.OLEDB.4.0',
> 'd:\test1.xls',
> NULL,
> 'Excel 5.0'
> EXEC sp_addlinkedsrvlogin test2, false, rhofing, null
> Then I try to select: select * from test2...test
> and I get the following error:
> Msg 7399, Level 16, State 1, Line 1
> The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "test2"
> reported an error. The provider did not give any information about the err
or.
> 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 "test2".
> Can anyone tell me what I am doing wrong? Thanks!
>