Query From Excel

SELECT * FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\test.xls', [Sheet1$])
When I run this query, I get this response:
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' does not contain table 'Sheet1$'.
The table either does not exist or the current user does not have permission
s
on that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain
the table: ProviderName='Microsoft.Jet.OLEDB.4.0', TableName='Sheet1$'].
Any idea why?David
The error message is pretty clear. Do you have a [Sheet1$]) ? Do you have
permissions to access the file?
"David Samson" <CaptainSlock@.nospam.nospam> wrote in message
news:9FF48606-8332-461E-9F23-662633D592F2@.microsoft.com...
> SELECT * FROM
> OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;Database=c:\test.xls', [Sheet1$])
> When I run this query, I get this response:
> Server: Msg 7314, Level 16, State 1, Line 1
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' does not contain table
> 'Sheet1$'.
> The table either does not exist or the current user does not have
> permissions
> on that table.
> OLE DB error trace [Non-interface error: OLE DB provider does not contain
> the table: ProviderName='Microsoft.Jet.OLEDB.4.0', TableName='Sheet1$'].
> Any idea why?|||Hi CaptainSlock,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you are executing the OPENROWSET but
failed to get the error.
I create a new Excel xls file and input some data on Sheet1, executing the
same query from Query Analyzer, I could get the correct result set as
expected. So would you please help me check the following settings?
1. Please confirm your Excel file contain a Sheet named "Sheet1" (Defautly,
a new Excel will have this sheet)
2. Please confirm you are using local SQL Server instance or the excel file
is located on the local disk
3. What account you are using have sufficient permission on this Excel file.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Yes, I have a C:\Test.xls with 3 columns, 10 rows, and one tab named Sheet1.
I am logged in with an ID that has Admin access to the PC that I am running
directly from. The sql server is on a separate box - Windows Server 2003,
SQL Server 2000 Enterprise.
"Michael Cheng [MSFT]" wrote:

> Hi CaptainSlock,
> Welcome to use MSDN Managed Newsgroup!
> From your descriptions, I understood you are executing the OPENROWSET but
> failed to get the error.
> I create a new Excel xls file and input some data on Sheet1, executing the
> same query from Query Analyzer, I could get the correct result set as
> expected. So would you please help me check the following settings?
> 1. Please confirm your Excel file contain a Sheet named "Sheet1" (Defautly
,
> a new Excel will have this sheet)
> 2. Please confirm you are using local SQL Server instance or the excel fil
e
> is located on the local disk
> 3. What account you are using have sufficient permission on this Excel fil
e.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||I have a C:\Test.xls with 3 columns, 10 rows, and one tab named Sheet1. I a
m
logged in with an ID that has Admin access to the PC that I am running
directly from. The sql server is on a separate box - Windows Server 2003,
SQL Server 2000 Enterprise.
"Uri Dimant" wrote:

> David
> The error message is pretty clear. Do you have a [Sheet1$]) ? Do you have
> permissions to access the file?
>
> "David Samson" <CaptainSlock@.nospam.nospam> wrote in message
> news:9FF48606-8332-461E-9F23-662633D592F2@.microsoft.com...
>
>|||Well, try the following query
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\test.xls;Extended Properties=Excel 8.0')...Sheet1$
Also take a look at Q306397 HOWTO: Use Excel w/ SQL Linked Servers &
Distributed Queries
http://support.microsoft.com/suppor...s/q306/3/97.asp
"David Samson" <CaptainSlock@.nospam.nospam> wrote in message
news:1212A046-58F7-4B00-8EFE-1984FD9F4814@.microsoft.com...
>I have a C:\Test.xls with 3 columns, 10 rows, and one tab named Sheet1. I
>am
> logged in with an ID that has Admin access to the PC that I am running
> directly from. The sql server is on a separate box - Windows Server 2003,
> SQL Server 2000 Enterprise.
> "Uri Dimant" wrote:
>|||Hi
You could try opening the file in DTS to see what sheet names you have, this
will also prove you have the correct access permissions or alternatively
create a new file and paste the data into that then there definately be a
sheet1.
Martin
"David Samson" wrote:
> Yes, I have a C:\Test.xls with 3 columns, 10 rows, and one tab named Sheet
1.
> I am logged in with an ID that has Admin access to the PC that I am runnin
g
> directly from. The sql server is on a separate box - Windows Server 2003,
> SQL Server 2000 Enterprise.
> "Michael Cheng [MSFT]" wrote:
>|||Dear all,
I've trying a very similar query and I obtain the following error from the
Sql Server 2005 CTP(June):
Msg 15501, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of
component 'Ad Hoc Distributed Queries' because this component is turned off
as part of the security configuration for this server. A system administrato
r
can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. Fo
r
more information about enabling 'Ad Hoc Distributed Queries', see "Surface
Area Configuration" in SQL Server Books Online.
From my sql server 2000 sp3a:
Servidor: mensaje 7314, nivel 16, estado 1, l_nea 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' does not contain table
'Clusterdb$'. The table either does not exist or the current user does not
have permissions on that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain
the table: ProviderName='Microsoft.Jet.OLEDB.4.0', TableName='Clusterdb$'].
Clusterdb exists as a sheet
"David Samson" wrote:
> Yes, I have a C:\Test.xls with 3 columns, 10 rows, and one tab named Sheet
1.
> I am logged in with an ID that has Admin access to the PC that I am runnin
g
> directly from. The sql server is on a separate box - Windows Server 2003,
> SQL Server 2000 Enterprise.
> "Michael Cheng [MSFT]" wrote:
>|||I found the problem. I was assuming that the reference to 'C:\test.xls' was
local. It's not, it's actually from the reference point of the server
itself. It makes sense, now that I think about it. So, once I copied the
file over to the C:\ of the database server, it worked like a champ. This i
s
the query I used:
select * FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\test.xls', [Sheet1$])
"John Bell" wrote:
> Hi
> You could try opening the file in DTS to see what sheet names you have, th
is
> will also prove you have the correct access permissions or alternatively
> create a new file and paste the data into that then there definately be a
> sheet1.
> Martin
> "David Samson" wrote:
>|||I found the problem. I was assuming that the reference to 'C:\test.xls' was
local. It's not, it's actually from the reference point of the server
itself. It makes sense, now that I think about it. So, once I copied the
test.xls file over to the C:\ of the database server, it worked like a champ
.
This is the query I used:
select * FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\test.xls', [Sheet1$])
"Uri Dimant" wrote:

> Well, try the following query
> SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
> 'Data Source=c:\test.xls;Extended Properties=Excel 8.0')...Sheet1$
>
> Also take a look at Q306397 HOWTO: Use Excel w/ SQL Linked Servers &
> Distributed Queries
> http://support.microsoft.com/suppor...s/q306/3/97.asp
>
> "David Samson" <CaptainSlock@.nospam.nospam> wrote in message
> news:1212A046-58F7-4B00-8EFE-1984FD9F4814@.microsoft.com...
>
>