Solved

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.

Posted on 2011-02-17
14
1,255 Views
Last Modified: 2012-05-11
I get this error message when I try to run a query that pulls data from an Excel Spreadsheet into a table.  The query is: cmd.CommandText = "INSERT INTO WBS " +
                          "SELECT Column1,Column2 " +
                          "FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', " +
                          "'Excel 8.0;Database=//ZINDATA1/EstimateWBS/template.xls', " +
                          "'SELECT * FROM [Sheet1$]')";
0
Comment
Question by:melli111
  • 8
  • 6
14 Comments
 
LVL 11

Expert Comment

by:Sudhakar Pulivarthi
ID: 34923357
Hi,

I think it is due to insufficient privilages.
http://support.microsoft.com/kb/814398
http://www.sql-server-helper.com/tips/read-import-excel-file-p01.aspx

Check out with the xls file by providing local path !!
0
 
LVL 11

Expert Comment

by:Sudhakar Pulivarthi
ID: 34923377
0
 
LVL 15

Author Comment

by:melli111
ID: 34952754
I have logged in with Administrator priviliges and still it does not work.  It has to be something with the connection string
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 11

Expert Comment

by:Sudhakar Pulivarthi
ID: 34957989
Hi,

Then did u checked by providing local path instead of UNC path for the xls file in the connection string.
0
 
LVL 15

Author Comment

by:melli111
ID: 34973103
Yes, I did this.  I used a local path and not a path on the network.  It has to be something with the connection string or the linked server, which I never did set up
0
 
LVL 11

Expert Comment

by:Sudhakar Pulivarthi
ID: 34976820
Hi,

Try using linked server: I think its problem with Server, which one u are using SQL Server
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q306397

Check this conversation discussed for the same scenario:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926&whichpage=11
0
 
LVL 11

Expert Comment

by:Sudhakar Pulivarthi
ID: 34976840
0
 
LVL 15

Author Comment

by:melli111
ID: 34979890
We created a linked server.  I now receive the following error:

"The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "LINKEDSERVER1" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "LINKEDSERVER1"."
0
 
LVL 11

Expert Comment

by:Sudhakar Pulivarthi
ID: 34980084
0
 
LVL 11

Expert Comment

by:Sudhakar Pulivarthi
ID: 34980135

Try creating linked server as below. if problem exists
--CREATE THE LINKSERVER FROM Excel
EXEC sp_addlinkedserver
@server = N'TestLinkServer',
@provider = N'Microsoft.Jet.OLEDB.4.0',
@srvproduct = N'Excel',
@datasrc = N'c:\test\book1.xls',
@provstr = N'Excel 8.0'
GO

--List all the sheets in the Excel
EXECUTE SP_TABLES_EX 'TestLinkServer'  -- throw the error message

--Solution
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'TestLinkServer',
@useself=N'False',
@locallogin=NULL,
@rmtuser=NULL,
@rmtpassword=NULL
0
 
LVL 15

Author Comment

by:melli111
ID: 34998907
Could it be something to do with the user that is authenticating is authenticating for SQL and not for Windows?
0
 
LVL 15

Author Comment

by:melli111
ID: 34999085
Sudhakar - I am convinced that the error is not coming from my SQL Server not having enough memory.  We recently upgraded the server with extra memory.  I have verified that the password for the user name is null when I check the Linked server.
0
 
LVL 15

Author Comment

by:melli111
ID: 34999102
Could the issue potentially be that we just upgraded our servers to 64 bit and that is causing a problem?
0
 
LVL 11

Accepted Solution

by:
Sudhakar Pulivarthi earned 500 total points
ID: 35003864
Hi,

Here one of the problem is with user authenticating has not having sufficient rights to access temp dir of SQL Server. Use the login which has high permissions and check...
http://www.sqlservercentral.com/Forums/Topic684266-149-2.aspx#bm1036514

When the Excel file is not closed, you’ll end up with the following error and more scenarios explained.
http://blog.hoegaerden.be/2010/03/29/retrieving-data-from-excel/
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question