Insert data from Excel file through OPENROWSET function.

Question

INSERT INTO XXX_temp( A,B) 
            SELECT  [A],[B] 
              FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 
                   'Excel 8.0;Database=C:\CLASUploadXLS\Client_Refresh_052611nh.xls', 
                   'SELECT * FROM [Client List$]')

But it is giving the below issue. I don't know how to fix it? Could you please help me in this regards. error:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error". Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked ser

I Tried the below solutions

restart of system
reconfugaring the openrow and opendatasource.
Execution of ole automation procedure

Still the issue is there. Could you please help me in this regard.

Answer 1

0

Check my answers to following questions:

import excel to sql
SSIS Excel dumping

You can receive this on X64 system, where you cannot use the 32 bit Microsoft.Jet.OLEDB.4.0.

To read the Excel file on x64 system you can use x64 version of Microsoft Access Database Engine 2010 Redistributable.. However it cannot coexist with 32 bit version of MS office if installed on the same machine. On Server where you do not have office installed, it should not be a problem.

answered Jun 30 '11 at 04:05 AM

Pavel Pawlowski
20.3k ● 5 ● 10 ● 20

(comments are locked)

Answer 2

0

You didn't specify which version of SQL Server you are running. Can you confirm you are using the CTE release of SQL Server 2005? This error was very popular prior to SP1 for SQL 2005.

answered Jun 30 '11 at 04:16 AM

Tim
31.5k ● 20 ● 30 ● 116

(comments are locked)

Answer 3

0

...linked server "(null)"......

this means it can't even 'find' what you are trying to use as a source, either because it is locked/denied/non-existant.

I would expect this error if the Excel sheet was already open.

If it is not open, then check the filename and path, and finally any permissions to that file and path.

answered Jun 30 '11 at 03:32 AM

Kev Riley ♦♦
46k ● 38 ● 43 ● 69

(comments are locked)

Insert data from Excel file through OPENROWSET function.

Follow this question

Related Questions