error with OpenRowSet

I'm trying to import a simple excel spreadsheet into MSDE using OpenRowSet,
but receive the following error:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
Connection Closed
here's the sql:
select *
into MyTable
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Default.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')
any help is appreciated...I think it should work if file name is correct. Check whether the file
name is correct
Madhivanan|||Hi
Try this one
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="d:\MyData.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Sheet1$
OR
Specify all columns
insert into MyTable (col1,col2)
SELECT col1,col2
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Default.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')
".bill" <wSweetman@.ucsd.edu> wrote in message news:O3rAPZfHFHA.3612@.TK2MSFTN
GP09.phx.gbl...
I'm trying to import a simple excel spreadsheet into MSDE using OpenRowSet,
but receive the following error:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
Connection Closed
here's the sql:
select *
into MyTable
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Default.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')
any help is appreciated...|||Do you have the file opened in Excel when you try to do this?
That would cause an error, but otherwise the syntax looks ok.
If the file C:\Default.xls exists and has a sheet named
Sheet1, it should work. Does the SQL Server
login have access to that file? What happens if you try
exec master..xp_cmdshell N'dir C:\*.xls'
Steve Kass
Drew University
.bill wrote:

>I'm trying to import a simple excel spreadsheet into MSDE using OpenRowSet,
but receive the following error:
>OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
>Connection Closed
>here's the sql:
>select *
>into MyTable
>FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;Database=C:\Default.xls;HDR=YES',
> 'SELECT * FROM [Sheet1$]')
>any help is appreciated...
>
>|||Hi there,
Has anyone found an answer to this yet?
I tried:
insert into tblTestSql (RowNo,RecNo,LdUserName,
Code,LogIn,LogOut)
SELECT RowNo,RecNo,LdUserName,
Code,LogIn,LogOut
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\ReleaseTwo\LogInandOut.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')
Which gives me
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: Unspecified error]
When I try:
exec master..xp_cmdshell N'dir c:\ReleaseTwo\LogInandOut.xls'
I get
The system cannot find the file specified.
Any ideas?
Paul
".bill" wrote:

> I'm trying to import a simple excel spreadsheet into MSDE using OpenRowSet
, but receive the following error:
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
> Connection Closed
> here's the sql:
> select *
> into MyTable
> FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;Database=C:\Default.xls;HDR=YES',
> 'SELECT * FROM [Sheet1$]')
> any help is appreciated..|||Hi Paul,
The file is in the local machine and you might have run the query in
the server which is not the local machine
Madhivanan|||Hi Madhivanan,
It dosn't matter whether the excel (or txt/csv) file is on the server on on
my local machine I get the same result.
Paul.
"Madhivanan" wrote:

> Hi Paul,
> The file is in the local machine and you might have run the query in
> the server which is not the local machine
> Madhivanan
>|||Paul,
How do you know the file exists?
If it does, and exec master..xp_cmdshell N'dir
c:\ReleaseTwo\LogInandOut.xls' fails,
it's not a problem with OPENROWSET. It sounds like the account used by
SQL Server does not have permission to access the Excel file.
SK
Paul in Harrow wrote:
>Hi there,
>Has anyone found an answer to this yet?
>I tried:
>insert into tblTestSql (RowNo,RecNo,LdUserName,
>Code,LogIn,LogOut)
>SELECT RowNo,RecNo,LdUserName,
>Code,LogIn,LogOut
>FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;Database=C:\ReleaseTwo\LogInandOut.xls;HDR=YES',
> 'SELECT * FROM [Sheet1$]')
>Which gives me
>Server: Msg 7399, Level 16, State 1, Line 1
>OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
>[OLE/DB provider returned message: Unspecified error]
>When I try:
>exec master..xp_cmdshell N'dir c:\ReleaseTwo\LogInandOut.xls'
>I get
>The system cannot find the file specified.
>Any ideas?
>Paul
>".bill" wrote:
>
>|||> How do you know the file exists?
Because I created it.

> If it does, and exec master..xp_cmdshell N'dir
> c:\ReleaseTwo\LogInandOut.xls' fails,
> it's not a problem with OPENROWSET. It sounds like the account used by
> SQL Server does not have permission to access the Excel file.
I've just shown that to my techie and he's looking blank! How do I/we find
out what permissions SQL Server has & how do I/we (proberly me) change them?
Many thanks
Paul.|||
Paul in Harrow wrote:

>Because I created it.
>
>
>I've just shown that to my techie and he's looking blank! How do I/we find
>out what permissions SQL Server has & how do I/we (proberly me) change them
?
>
>
It might be as simple as: navigate to the file in Windows Explorer,
right-click, go to the security tab, and make sure the file is available
to the account under which the SQL Server service is running.
But managing Windows accounts is not my strong point, so you could also
look at "Setting up Windows Services Accounts" in Books Online and
see what you find on Usenet:
http://groups.google.co.uk/groups? ...missions%20file
http://www.google.co.uk/search? q=%...missions%20file
For the openquery statement to work, you should be able to log on to
Windows with the same account used by the SQL Server service and
then access the file.
SK

>Many thanks
>Paul.
>