Export SQL QueryResult to Excell file on Server without installing Office

Question

I want write result rows of a query to a excell file:

INSERT into OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
'Excel 12.0;Database=C:\temp\testing.xlsx;', 
'SELECT Id,CompanyName FROM [Sheet1$]') 
select Id,CompanyName   from tbl_Company

But When Running This query following error occurs :

Msg 7302, Level 16, State 1, Line 3 Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

What is done before running this code :

1 - I installed "2007 Office System Driver: Data Connectivity Components"

2 - Executed Configuration Script for using excell :

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

3 - Change Login Account for SQL Server Service to local account

4 - Added Full Access permission on Excell file folder to local account

5 - Restarted SQL Service

But my problem remains

Server : Windows Server 2008

MS Office not installed on server

SQL Server 2014 64bit SP1

score 0 · Answer 1 · answered May 09 '16 at 10:29

I had a mistake :

I should run this configuration script

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

also you should be sure to install 64bit version of Access Database Engine (if your sql server is 64bit).

Export SQL QueryResult to Excell file on Server without installing Office

1 Answers1