excel - Export SQL QueryResult to Excell file on Server without installing Office -
i want write result rows of query excell file:
insert openrowset('microsoft.ace.oledb.12.0', 'excel 12.0;database=c:\temp\testing.xlsx;', 'select id,companyname [sheet1$]') select id,companyname tbl_company
but when running query following error occurs :
msg 7302, level 16, state 1, line 3 cannot create instance of ole db provider "microsoft.ace.oledb.12.0" linked server "(null)".
what done before running code :
1 - installed "2007 office system driver: data connectivity components"
2 - executed configuration script 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 sql server service local account
4 - added full access permission on excell file folder local account
5 - restarted sql service
but problem remains
server : windows server 2008
ms office not installed on server
sql server 2014 64bit sp1
i had mistake :
i should run 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 should sure install 64bit version of access database engine (if sql server 64bit).
Comments
Post a Comment