Geek asks and answers

Excel in SQL Server with Microsoft.ACE.OLEDB.12.0

I get the following error when trying to open an Excel file in SQL Server 2008 r2 64-bit:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" 
reported an error. The provider did not give any information about the 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)".

I am using the following query:

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; 
HDR=NO; IMEX=1; Database=\\filepath\filename.xlsx', 'SELECT * FROM [Sheet1$]')

The funny thing is that the DBA can run it without problems. I went through and completed the following queries:

sp_configure 'Show Advanced Options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO

An account that works looks like it has access to sa. What could be causing this problem?

+5
source share
3 answers

Have you tried (as a test) to copy an Excel file to the C: \ SQL Server drive and execute a query along this path?

What happens when you go to the server and open this path in the Explorer / run: \ filepath \ filename.xlsx dialog box ?

: exec master..xp_cmdshell 'dir'\filepath\filename.xlsx '?

, .

, /, DBA .

+4

... xp_cmdshell. - , ,

SP_CONFIGURE 'XP_CMDSHELL',1
GO             
RECONFIGURE

InProcess ACE:

USE [master]
GO
EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
GO

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
GO

:

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE

, . , , excel SQL-, :

SELECT * INTO TargetTableName FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                         'Excel 12.0;Database=FilePath\fileName.xlsx;HDR=YES',
                         'SELECT * FROM [sheetName$]')

, xls Jet Driver ACE. TargetTableName . :)

+4

SQL Server Management Studio. Services.msc , .

SQL Server .

" " ​​ / .

, , OK.

.

, , .

SQL Server Management Studio , , .

.

[] 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 GO

-1

More articles:


All Articles