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,175 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help with SQL joins 9 54
Table create permissions on SQL Server 2005 9 43
INSERT DATE FROM STRING COLUMN 18 59
Getting max record but maybe not use Group BY 2 32
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
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 shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

828 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