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,254 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

813 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now