Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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
Medium Priority
?
1,266 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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

Major Serverless Shift

Comparison of major players like AWS, Microsoft Azure, IBM Bluemix, and Google Cloud Platform

Question has a verified solution.

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

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

670 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