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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

739 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