Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
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
Medium Priority
?
1,243 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 1000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

609 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