Import Excel data into MS SQL Server

Hi,
We are trying to import Excel data (Excel 2002) into some SQL server tables.
However, we have tried several things but they all seem to fail.
1) The following command on our production server (Windows NT SP6a - MS SQL
Server 2000 SP3) fails when we run it in Query Analyzer:
select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Excel
8.0;Database=\\server\file.xls', [Sheet1$])
However when we run this command in Query Analyzer on our Client (Windows
XP - MS SQL Server 2000 SP3) and refer to the SAME file, which is an Excel
file on our production server, we get the Excel data.
The error returned on our production server is:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider
did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: The provider did not give
any information about the error.].
2) The following command on our production server (Windows NT SP6a - MS SQL
Server 2000 SP3) fails when we run it in Query Analyzer:
select * from OpenRowset('MSDASQL', 'Driver=Microsoft Excel Driver
(*.xls);DBQ=\\server\file.xls', 'select * from [Sheet1$]')
However when we run this command in Query Analyzer on our Client (Windows
XP - MS SQL Server 2000 SP3) and refer to the SAME file, which is an Excel
file on our production server, we get the Excel data.
The error returned on our production server is:
Server: Msg 7399, Level 16, State 1, Line 8
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Excel Driver] Cannot
open database '(unknown)'. It may not be a database that your application
recognizes, or the file may be corrupt.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x80004005: ].
3) If I save the Excel file in Excel 4.0 (single worksheet) format and run
the following command on our production server then we get the Excel data.
select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Excel
4.0;Database=\\server\file.xls', [file$])
I've checked several sources on the internet and most of them seem to
conclude that this error is permission related but I'm sure that the
production server does have access to the Excel file. I have a feeling that
some setting (registry) is missing or is incorrect or that a file is missing
or has the wrong version (it seem that we can access older Excel files).
All help is appreciated,
Alain Sienaert
Cortex
Alain,
I am using DTS to export data from Excel. I have similar case like you, exporting data from sharedrive\Excel. However I am not using OpenRowset function. I have just mapped Each excel column to SQL table. we are using special Id when DTS schedular invokes
that job, So major special setting we did is to have access for DTS schedular ID to that Shared drive and excel.
just sharing my thought..not sure it will help..
"Alain Sienaert" wrote:

> Hi,
> We are trying to import Excel data (Excel 2002) into some SQL server tables.
> However, we have tried several things but they all seem to fail.
> 1) The following command on our production server (Windows NT SP6a - MS SQL
> Server 2000 SP3) fails when we run it in Query Analyzer:
> select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Excel
> 8.0;Database=\\server\file.xls', [Sheet1$])
> However when we run this command in Query Analyzer on our Client (Windows
> XP - MS SQL Server 2000 SP3) and refer to the SAME file, which is an Excel
> file on our production server, we get the Excel data.
> The error returned on our production server is:
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider
> did not give any information about the error.
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: The provider did not give
> any information about the error.].
>
> 2) The following command on our production server (Windows NT SP6a - MS SQL
> Server 2000 SP3) fails when we run it in Query Analyzer:
> select * from OpenRowset('MSDASQL', 'Driver=Microsoft Excel Driver
> (*.xls);DBQ=\\server\file.xls', 'select * from [Sheet1$]')
> However when we run this command in Query Analyzer on our Client (Windows
> XP - MS SQL Server 2000 SP3) and refer to the SAME file, which is an Excel
> file on our production server, we get the Excel data.
> The error returned on our production server is:
>
> Server: Msg 7399, Level 16, State 1, Line 8
> OLE DB provider 'MSDASQL' reported an error.
> [OLE/DB provider returned message: [Microsoft][ODBC Excel Driver] Cannot
> open database '(unknown)'. It may not be a database that your application
> recognizes, or the file may be corrupt.]
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
> returned 0x80004005: ].
>
> 3) If I save the Excel file in Excel 4.0 (single worksheet) format and run
> the following command on our production server then we get the Excel data.
> select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Excel
> 4.0;Database=\\server\file.xls', [file$])
>
> I've checked several sources on the internet and most of them seem to
> conclude that this error is permission related but I'm sure that the
> production server does have access to the Excel file. I have a feeling that
> some setting (registry) is missing or is incorrect or that a file is missing
> or has the wrong version (it seem that we can access older Excel files).
> All help is appreciated,
> Alain Sienaert
> Cortex
>
>
>
|||Alain,
I am using DTS to export data from Excel. I have similar case like you, exporting data from sharedrive\Excel. However I am not using OpenRowset function. I have just mapped Each excel column to SQL table. we are using special Id when DTS schedular invokes
that job, So major special setting we did is to have access for DTS schedular ID to that Shared drive and excel.
just sharing my thought..not sure it will help..
"Alain Sienaert" wrote:

> Hi,
> We are trying to import Excel data (Excel 2002) into some SQL server tables.
> However, we have tried several things but they all seem to fail.
> 1) The following command on our production server (Windows NT SP6a - MS SQL
> Server 2000 SP3) fails when we run it in Query Analyzer:
> select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Excel
> 8.0;Database=\\server\file.xls', [Sheet1$])
> However when we run this command in Query Analyzer on our Client (Windows
> XP - MS SQL Server 2000 SP3) and refer to the SAME file, which is an Excel
> file on our production server, we get the Excel data.
> The error returned on our production server is:
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider
> did not give any information about the error.
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: The provider did not give
> any information about the error.].
>
> 2) The following command on our production server (Windows NT SP6a - MS SQL
> Server 2000 SP3) fails when we run it in Query Analyzer:
> select * from OpenRowset('MSDASQL', 'Driver=Microsoft Excel Driver
> (*.xls);DBQ=\\server\file.xls', 'select * from [Sheet1$]')
> However when we run this command in Query Analyzer on our Client (Windows
> XP - MS SQL Server 2000 SP3) and refer to the SAME file, which is an Excel
> file on our production server, we get the Excel data.
> The error returned on our production server is:
>
> Server: Msg 7399, Level 16, State 1, Line 8
> OLE DB provider 'MSDASQL' reported an error.
> [OLE/DB provider returned message: [Microsoft][ODBC Excel Driver] Cannot
> open database '(unknown)'. It may not be a database that your application
> recognizes, or the file may be corrupt.]
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
> returned 0x80004005: ].
>
> 3) If I save the Excel file in Excel 4.0 (single worksheet) format and run
> the following command on our production server then we get the Excel data.
> select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Excel
> 4.0;Database=\\server\file.xls', [file$])
>
> I've checked several sources on the internet and most of them seem to
> conclude that this error is permission related but I'm sure that the
> production server does have access to the Excel file. I have a feeling that
> some setting (registry) is missing or is incorrect or that a file is missing
> or has the wrong version (it seem that we can access older Excel files).
> All help is appreciated,
> Alain Sienaert
> Cortex
>
>
>