Excel 2003 Linked Server

Dear All,
Please can you help with a problem that I am having which I have
spent
all day trying every conceivable permutation of options to solve
without success. I am sure I have done this kind of thing before
using
sqlserver 2000 without problem.
I am using sqlserver 2005 express (SP2) and am trying to query an
excel spreadsheet using linked server and distributed queries.
I have definitely configured 2005 express to enable distributed
queries and have tried all of these query types but always get the
same error message about not being able to initialise data source.
SELECT * FROM XLTEST_SP...Sheet1$
SELECT * FROM OPENQUERY(XLTEST_SP, 'SELECT * FROM [Sheet1$]')
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data
Source=c:
\EXCEL\book1.xls;Extended Properties=Excel 8.0')...Sheet1$
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel
8.0;Database=c:\EXCEL\book1.xls', Sheet1$)
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Data Source=c:
\EXCEL\book1.xls;Extended Properties=Excel 8.0', Sheet1$)
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'DataSource=c:
\EXCEL\book1.xls;Extended Properties=Excel 8.0', Sheet1$)
Please can somebody explain where I am going wrong?
Thanks in anticipation,
Pete
Try this syntax:
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\book1.xls', Sheet1$)
I get the ISAM error with your openrowset syntax. See this KB:
http://support.microsoft.com/kb/306397
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"pete_bristol" <p.e.t.e@.lineone.net> wrote in message
news:18c8fc9f-d7ba-4582-8ed1-8f16e37a8ad0@.z17g2000hsg.googlegroups.com...
> Dear All,
> Please can you help with a problem that I am having which I have
> spent
> all day trying every conceivable permutation of options to solve
> without success. I am sure I have done this kind of thing before
> using
> sqlserver 2000 without problem.
>
> I am using sqlserver 2005 express (SP2) and am trying to query an
> excel spreadsheet using linked server and distributed queries.
>
> I have definitely configured 2005 express to enable distributed
> queries and have tried all of these query types but always get the
> same error message about not being able to initialise data source.
>
> SELECT * FROM XLTEST_SP...Sheet1$
> SELECT * FROM OPENQUERY(XLTEST_SP, 'SELECT * FROM [Sheet1$]')
> SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data
> Source=c:
> \EXCEL\book1.xls;Extended Properties=Excel 8.0')...Sheet1$
> SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel
> 8.0;Database=c:\EXCEL\book1.xls', Sheet1$)
> SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Data Source=c:
> \EXCEL\book1.xls;Extended Properties=Excel 8.0', Sheet1$)
> SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'DataSource=c:
> \EXCEL\book1.xls;Extended Properties=Excel 8.0', Sheet1$)
>
> Please can somebody explain where I am going wrong?
>
> Thanks in anticipation,
>
> Pete
|||On 24 Feb, 23:58, "jason" <jason-r3m...@.statisticsio.com> wrote:
> Try this syntax:
> SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> X X'Excel 8.0;Database=c:\book1.xls', Sheet1$)
> I get the ISAM error with your openrowset syntax. See this KB:http://support.microsoft.com/kb/306397
> --
> Jason Massie
> www:http://statisticsio.com
> rss:http://feeds.feedburner.com/statisticsio
> "pete_bristol" <p.e...@.lineone.net> wrote in message
> news:18c8fc9f-d7ba-4582-8ed1-8f16e37a8ad0@.z17g2000hsg.googlegroups.com...
>
>
>
>
>
> - Show quoted text -
Jason,
Thank you for taking the time to reply.
I tried your suggestion but unfortunately it gave the same error
message as all the others:-
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
returned message "Unspecified 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)".
I have just tried all of my previous attempts on a different computer
using XP instread of vista, having downloaded all the latest versions
of SQLSERVER and get the same message. Perhaps it just cannot be done?
Thanks again. Any other suggestions?
Regards,
Pete