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,258 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

 
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: 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

How our DevOps Teams Maximize Uptime

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

Question has a verified solution.

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

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

688 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