Solved

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Posted on 2011-09-21
5
1,205 Views
Last Modified: 2012-05-12
Heelo,

I am trying to export the data from the SQL table to Excel file using stored procedure. But I am keep getting the following error.

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=E:\DONOTDELETE\Report\MonthlyReportRegionNE.xls;','SELECT FirstName, LastName, WeekofDate, CallDate, CompanyName, ContactName, Address, City, State, Zip FROM [Preplan$]') SELECT DMFirstName, DMLastName, WeekofDate, CallDate, CompanyName, ContactName, Address, City, State, Zip FROM [report].[preplanRpt]

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".


I have tried few things but still getting the same error.
1) Change the folder (\Report) permission to FULL Control
2) Restart the SQL server
3) Check to see the openrowset setting is enable by going through surface area configuration.

Not sure what else I should check on that. Any help would be greatly appreciated.

Thanks,
andrishelp
0
Comment
Question by:andrishelp
[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
  • 4
5 Comments
 
LVL 21

Accepted Solution

by:
JestersGrind earned 250 total points
ID: 36573850
This error occurs when the user that you are running the query as doesn't have access to the temp directory of the account that SQL is running as.  Please take a look at this MS article.

http://support.microsoft.com/kb/814398

Greg

0
 

Author Comment

by:andrishelp
ID: 36574051
Greg,
Looks like all setting are correct for temp folder. But we can not restart the server until noon CST. Hope that solve the issue. Any other suggestions??
0
 

Author Comment

by:andrishelp
ID: 36574073
We are running Microsoft Windows Server 2003 Service Pack 2 (SP2) 64bit version. Is there any different settings that we need to set up for that to work?
0
 

Assisted Solution

by:andrishelp
andrishelp earned 0 total points
ID: 36576427
Installed new driver...
http://omensblog.blogspot.com/2008/05/opendatasource-or-openrowset-linked.html

This worked well. As per scheduled, the server has been restarted. No errors after that so far

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=E:\DONOTDELETE\Report\MonthlyReportRegionNE.xls;','SELECT FirstName, LastName, WeekofDate, CallDate, CompanyName, ContactName, Address, City, State, Zip FROM [Preplan$]') SELECT DMFirstName, DMLastName, WeekofDate, CallDate, CompanyName, ContactName, Address, City, State, Zip FROM [report].[preplanRpt]

Thanks.
Andrishelp
0
 

Author Closing Comment

by:andrishelp
ID: 36597801
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

707 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