| Author |
Topic |
|
Nazim
A custom title
1408 Posts |
 Posted - 2002-05-01 : 09:12:20
|
| I have been trying hard to query a excel sheet using Openrowset. it always gives me a Error stating(though there is sheet1$ and am able to access it thru VB Code).Server: Msg 7314, Level 16, State 1, Line 1OLE DB provider 'Microsoft.Jet.OLEDB.4.0' does not contain table 'sheet1$'.I have tried all the options , listed in this KB article http://support.microsoft.com/default.aspx?scid=kb;en-us;Q306397 .SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=c:\book1.xls;Extended Properties=Excel 8.0')...Sheet1$SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\book1.xls', Sheet1$)SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Data Source=c:\book1.xls;Extended Properties=Excel 8.0', Sheet1$)Any Ideas?--------------------------------------------------------------Edited by - Nazim on 05/01/2002 09:14:11 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-05-01 : 09:50:01
|
The first two of the three worked for me in SQL 7 sp3 . . .The third gave me . . .quote:
Server: Msg 7399, Level 16, State 1, Line 1OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. [OLE/DB provider returned message: Could not find installable ISAM.]
(I know that doesn't help you much, other than to say the syntax isn't necessarily wrong . . .)<O> |
 |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-05-01 : 09:57:45
|
| Jason which version of Excel are you using ??-------------------------------------------------------------- |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-01 : 10:04:24
|
| Is the "$" actually part of the sheet name? I would recommend changing the name of the sheet (if you can) and eliminate the "$", see if that fixes it. I know that Excel syntax uses the "$" as a separator and it could be misinterpreting it as such.Edited by - robvolk on 05/01/2002 10:04:56 |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-05-01 : 10:07:18
|
I have already tried that Rob  -------------------------------------------------------------- |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-01 : 10:11:50
|
| Have you tried installing the latest MDAC drivers? (which version of MDAC are you running anyway?) I know they took the Jet drivers out of the default MDAC installation, so if you're using MDAC 2.6 or higher, the drivers might be incomplete. |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-05-01 : 10:13:34
|
| Sorry guys, it works fine in Sql Server 2000 with sp 2. this could be because in my PC i have Sql Server 2000 installed and i was connected to Sql 7 database , but still thinks why should that pose any problem.-------------------------------------------------------------- |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-05-01 : 10:16:02
|
| Rob, how will i check which version of MDAC am using. i just checked in Components in VB and found ADO 2.7 and thought that is my MDAC version. is there any other way.-------------------------------------------------------------- |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-05-01 : 10:21:56
|
I just checked in Registry and my version is 2.70.7430.7 .Did anyone else came with a similar problem . quote:
this could be because in my PC i have Sql Server 2000 installed and i was connected to Sql 7 database , but still thinks why should that pose any problem.
--------------------------------------------------------------Edited by - Nazim on 05/01/2002 10:38:13 |
|
|
|
|
Openrowset Problem!!!!!!!!