samedi 28 mars 2015

Cannot read Excel file from SQL Server


EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO

EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO

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


Reading Excel



DECLARE @SQLconnect VARCHAR(8000)

SET @SQLconnect = 'SELECT * FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',
''Excel 8.0;Database=D:\WAGES.xlsx;'',
''SELECT * FROM [Sheet2$]'')'
EXEC (@SQLconnect)


Exception



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 reported an unexpected catastrophic failure.


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)".



Sometimes it gives error sometimes it goes in infinite loop of execution.


I am getting this error after installing SQL Server 2008 R2 over SQL Server 2012.



  • OS Windows 7 32 bit os

  • SQL Server 2008 R2 and SQL Server 2012 installed


Aucun commentaire:

Enregistrer un commentaire