OPENROWSET with Excel file

Question

I want to execute simple statement:

SELECT * FROM 
OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Text;Database=C:\Temp\;','SELECT * FROM [test.csv]')

And suddenly I get this message today morning:

Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'MICROSOFT.JET.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

It was working till today morning!

Here is my server specs: Windows 2008 R2 64 bit SQL Server 2008 64 bit

I've installed AccessDatabaseEngine_x64.exe.

Sql Server is running under LocalService account. I've set Everyone to have FullControl permission to "C:\Temp" as well as "C:\Windows\ServiceProfiles\LocalService\AppData\Local".

Is there anything I missed? I'm really confused...

Edited: I've also executed these statements:

sp_configure ‘show advanced options’, 1;
 GO
 RECONFIGURE;
 GO
 sp_configure ‘Ad Hoc Distributed Queries’, 1;
 GO
 RECONFIGURE;
 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

I've also tested ACE.OLEDB.12.0 with administrator account:

SELECT * FROM  
OPENROWSET('MICROSOFT.ACE.OLEDB.12.0','Text;Database=C:\Temp\;','SELECT * FROM [test.csv]')

There is another 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)".

Finally I found it: I ran ProcMon and I saw that Sql Server wants to access to F:\Windows Temp\ and the folder does not exist! I created the folder and the issue is solved. But, I never had such a folder!!!

Geronto

Are you using ACE.OLEDB.12.0 or JET.OLEDB.4.0? On x64 version of the server you'll need to use ACE.OLEDB.12.0. – Troostite

I'm using ACE.OLEDB.12.0 and it was working for a year before. Is there any Windows Update which cause this issue? I just tried JET.OLEDB.4.0 to test written in some blogs. – Geronto

Do you start SQL Server service under user account security context? – Troostite

It's running under Local Service account. – Geronto

I got the exact same error reading CSV files until setting permissions on TEMP folder of the user account SQL Server runs under. More info: blogs.msdn.com/b/spike/archive/2008/07/23/… – Troostite

I've set Full Control for Everyone to the folder. – Geronto

Answer 1

Finally I found it: I ran ProcMon and I saw that SQL Server wants to access to F:\Windows Temp\ and the folder does not exist! I created the folder and the issue is solved. But, I never had such a folder!!!

Geronto