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

Popular posts from this blog

javascript - Laravel datatable invalid JSON response -

java - Exception in thread "main" org.springframework.context.ApplicationContextException: Unable to start embedded container; -

sql server 2008 - My Sql Code Get An Error Of Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value '8:45 AM' to data type int -