export sql query result to excel

forum,

i'm stuck issue after migrating our ps report jobs win 2008 server win 2012 server. have extract generates csv file , converts excel , mails analysts . have used following link in working works fine when run manually when scheduled sql server agent job nothing , not fail though 

# convert csv file excel
# reference : http://gallery.technet.microsoft.com/scriptcenter/da4c725e-3487-42ff-862f-c022cf09c8fa

both servers identical nothing has changes @ permission level except os different..

sample code:

$code = @"
select top 2000 
table
"@

$csvfilepath = "c:\location\query.csv" 

$excelfilepath = "d:\location\query.xls"

if (test-path $excelfilepath) 
{
  remove-item $excelfilepath
}
#deleting file 
$instancename = "server" # instance name
$databasename = "db";             #db name
$oresults = invoke-sqlcmd -query $code -serverinstance $instancename -database $databasename -querytimeout 3000 

# output csv
#write-host "saving query results in csv format..." 
$oresults | export-csv $csvfilepath -notypeinformation -force

# convert csv file excel
# reference : http://gallery.technet.microsoft.com/scriptcenter/da4c725e-3487-42ff-862f-c022cf09c8fa
#write-host "converting csv output excel..." 

$oexcel = new-object -comobject excel.application 
$oexcel.visible = $false 
$oexcel.displayalerts=$false 
$workbook = $oexcel.workbooks.open($csvfilepath) 
$worksheet = $workbook.worksheets.item(1) 
$resize = $worksheet.usedrange 
$resize.entirecolumn.autofit() | out-null 
$xlexcel8 = 56
$workbook.saveas($excelfilepath,$xlexcel8) 
$workbook.close()
$oexcel.quit() 
$oexcel = $null


$attach += $bcexcelfilepath

on old server still works fine have migrate new server . script runs fine when run manually when running agent not intended to.

what it doesn't do?

what account sqlagent running as?

office applications not allowed run service or called service.  ws2012 , office 2016 may make doing impossible.

a data table or query result can , correctly exported excel using ssis , preferred method doing this.

your script has no error logging how able tell why failing?

see this: https://support.microsoft.com/en-us/kb/257757

---

\_(ツ)_/

Windows Server  >  Windows PowerShell