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,152 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
  • 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

943 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now