Cannot read Excel file from SQL Server with multiple versions of SQL Server

Question

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.

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

OS: Windows 7 32-bit
SQL Server 2008 R2 and SQL Server 2012 installed

Have you tried just executing that directly, not in inside dynamic SQL? Any reason you are using dynamic SQL? IS D:\WAGES.xlsx on the SQL Server? — Nick.McDermaid
I have tried both way. And yes Wages.xlsx on D:\ and SQL Server on same machine — Rajeshirke

Unknown Unknown · Accepted Answer · 2015-03-29T07:11:48

The SQL Server Error Message if a user have no rights for SQL Server TEMP directory:

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

Grant rigths to TEMP directory
(i.) This step is required only for 32-bit SQL Server with any OLE DB provider

The main problem is that an OLE DB provider creates a temporary file during the query in the SQL Server temp directory using credentials of a user who run the query.

The default directory for SQL Server is a default directory for SQL Server service account.

If SQL Server is run under Network Service account the temp directory is like:

C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp

If SQL Server is run under Local Service account the temp directory is like:

C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp

Microsoft recommends two ways for the solution:

A change of SQL Server TEMP directory and a grant of full rights for all users to this directory. Grant of read/write rights to the current SQL Server TEMP directory.

See details: PRB: "Unspecified error" Error 7399 Using OPENROWSET Against Jet Database

Usually only few accounts are used for import operations. So we can just add rights for these accounts.

For example, icacls utility can be used for the rights setup:

icacls C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp /grant vs:(R,W)

For more information follow this Link

Cannot read Excel file from SQL Server with multiple versions of SQL Server

2 Answers