November 8, 2011 at 12:27 pm
Hi All,
We have multiple servers running Excel imports/exports via OpenDataSource and linked servers. All using ACE.OLEDB 12.00.
All except one new one (Server 2008/32bit SQL 2008 Web Edition) running under WMWare. It positively, absolutely refuses to run no matter what the query, which excel file or file location.
The SQL Error is 7303, which is either a syntax or rights issue.
The Syntax is fine and the file rights are set to full control to everyone.
Our test example(which works on all the other servers)
select one,two
FROM OpenDataSource( 'Microsoft.ACE.OLEDB.12.0','Data Source=C:\Excel\test.xlsx;Extended properties="Excel 12.0;HDR=yes;IMEX=1";')...Sheet1$
The Error:
OLE DB provider "Microsoft.ACE.OLEDB.12.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.ACE.OLEDB.12.0" for linked server "(null)".
SQL Command Configuration Script
Execute sp_configure 'show advanced options', 1
reconfigure
Execute sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure
Execute sp_configure 'show advanced options', 1
RECONFIGURE
Execute sp_configure 'Ole Automation Procedures', 1
RECONFIGURE
Execute sp_configure'xp_cmdshell',1
RECONFIGURE
USE master
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
I'm thinking (hoping) it's a simple Windows Server or SQL Server Configuration problem.
Our last resort is scrap the whole Server and build a new one from scratch and hope the problem goes away on it own
Thanks
Bill Plander
November 8, 2011 at 12:48 pm
Have you made sure you have that connection driver installed?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 8, 2011 at 1:00 pm
Yeah, in fact I've tried previous versions all the way back to Jet, plus Excel is installed on the machine.
It seems like SQL is failing on any attempt to connect to a file, even though it's setup up correctly.
Some part is either broken or missing
thanks
November 9, 2011 at 6:45 am
Is the file local to the server, or on a separate machine?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 9, 2011 at 6:59 am
select one,two
FROM OpenDataSource( 'Microsoft.ACE.OLEDB.12.0','Data Source=C:\Excel\test.xlsx;Extended properties="Excel 12.0;HDR=yes;IMEX=1";')...Sheet1$
The Error:
OLE DB provider "Microsoft.ACE.OLEDB.12.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.ACE.OLEDB.12.0" for linked server "(null)".
Can you please try following?
*** The directory in which the .xls resides MUST HAVE Everyone with FullControl at the share level.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply