x

Insert data from Excel file through OPENROWSET function.

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

  1. restart of system
  2. reconfugaring the openrow and opendatasource.
  3. Execution of ole automation procedure
Still the issue is there. Could you please help me in this regard.
more ▼

asked Jun 29, 2011 at 10:26 PM in Default

Amardeep gravatar image

Amardeep
1.3k 87 88 89

Do you have the excel document closed before running the query?

32-bit or 64-bit?

Does the SQL Server service account have permission to the file (full control)?

Do you have an up-to-date Jet driver? - http://support.microsoft.com/kb/239114

Can you create a linked server to this file and reference it that way?

Can you use SSIS/DTS instead of T-SQL (which version of SQL Server are you using)?
Jun 29, 2011 at 11:08 PM KenJ
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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.][3]. 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.

[3]: http://www.microsoft.com/downloads/en/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en
more ▼

answered Jun 30, 2011 at 04:05 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

(comments are locked)
10|1200 characters needed characters left
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.
more ▼

answered Jun 30, 2011 at 04:16 AM

Tim gravatar image

Tim
36.4k 39 41 139

(comments are locked)
10|1200 characters needed characters left

...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.
more ▼

answered Jun 30, 2011 at 03:32 AM

Kev Riley gravatar image

Kev Riley ♦♦
54.3k 47 49 76

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x117
x27

asked: Jun 29, 2011 at 10:26 PM

Seen: 3318 times

Last Updated: Jun 30, 2011 at 03:44 AM