• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1320
  • Last Modified:

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.

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
melli111
Asked:
melli111
  • 8
  • 6
1 Solution
 
Sudhakar PulivarthiProject Lead - EngineeringCommented:
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
 
Sudhakar PulivarthiProject Lead - EngineeringCommented:
0
 
melli111SharePoint Administrator / DeveloperAuthor Commented:
I have logged in with Administrator priviliges and still it does not work.  It has to be something with the connection string
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Sudhakar PulivarthiProject Lead - EngineeringCommented:
Hi,

Then did u checked by providing local path instead of UNC path for the xls file in the connection string.
0
 
melli111SharePoint Administrator / DeveloperAuthor Commented:
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
 
Sudhakar PulivarthiProject Lead - EngineeringCommented:
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
 
Sudhakar PulivarthiProject Lead - EngineeringCommented:
0
 
melli111SharePoint Administrator / DeveloperAuthor Commented:
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
 
Sudhakar PulivarthiProject Lead - EngineeringCommented:

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
 
melli111SharePoint Administrator / DeveloperAuthor Commented:
Could it be something to do with the user that is authenticating is authenticating for SQL and not for Windows?
0
 
melli111SharePoint Administrator / DeveloperAuthor Commented:
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
 
melli111SharePoint Administrator / DeveloperAuthor Commented:
Could the issue potentially be that we just upgraded our servers to 64 bit and that is causing a problem?
0
 
Sudhakar PulivarthiProject Lead - EngineeringCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now