PowerShell script via scheduled task not working

Question

I have a powershell script that works perfectly when I manually execute it from the command prompt using:

powershell -NoLogo -NonInteractive -File "D:\ServerFolders\Company\Scripts\Powershell Scripts\SendMonthlyGrowthRateReport.ps1"

The script is opening an excel spreadsheet, pulling some data from SQL into the spreadsheet, saves a copy of the spreadsheet with the current Date on it, and then emails the spreadsheet out via SQL sp_send_dbmail. It also logs current status to a log file.

Now I setup a scheduled task on Windows 2012 R2 to execute the above command once a month, but when I manually run the task or try to let it kick itself off, only the log file is generated, the spreadsheet isn't saved and email never goes out.
Any Ideas?

UPDATE: below is the code in the script, I should also note that powershell returns exit code 0 when executed through the scheduled task...

$DebugPreference = 2
$VerbosePreference = 2
$WarningPreference = 2
#param([string] $TemplateFilePath = "TemplateFilePath", 
#    [string]$StorageRoot = "StorageRoot",
#    [string]$NameFormat = "NameFormat",
#    [string]$ReportType = "ReportType",
#    [string] $SendReportTo = "SendReportTo")

$TemplateFilePath = 'D:\ServerFolders\Company\Spreadsheets\templates\DatabaseGrowthTemplate.xlsx'
$StorageRoot = 'D:\ServerFolders\Company\Spreadsheets'
$NameFormat = '%type% Database Growth Report %date%.xlsx'
$ReportType = "Monthly"
$SendReportTo ="*******@someDomain.com"

$Date = get-Date
$LogFile = "D:\ServerFolders\Company\SyncLogs\MonthlyGrowthReport" + $Date.toString("yyyy-MM-dd hhmmss") + ".log"

Function LogWrite
{
   Param ([string]$logstring)
   $date =  get-date
   $DateString = $date.toString("yyyy-MM-dd hh:mm:ss")
   $FileValue = $DateString +' - ' + $logstring
   if($LogFile -ne $null -and $LogFile -ne "")
   {
        Add-content $Logfile -value $FileValue
   }

   Write-Host $FileValue
}

if ($ReportType -ne 'Weekly' -and $ReportType -ne 'Monthly' -and $ReportType -ne 'Quarterly' -and $ReportType -ne 'Yearly')
{
    Write-Host "Valid options for ReportType parameter are 'Weekly', 'Monthly', 'Quarterly', or 'Yearly'"
    exit
}

$Date = get-Date
$DateString = $Date.ToString("yyyy-MM-dd")

$FromDate = $DateString
$FileName = $NameFormat.Replace("%date%", $DateString)
$FileName = $FileName.Replace("%type%", $ReportType)

$Destination = join-path $StorageRoot $FileName

$LogWrite = "Generate Destination File of " + $Destination
LogWrite $LogWrite

$IncrementType ="Days"
if ($ReportType -eq "Weekly"){
    $IncrementType = "Weeks"
} 
if ($ReportType -eq "Monthly"){
    $IncrementType = "Months"
} 
if ($ReportType -eq "Quarterly"){
    $IncrementType = "Quarters"
} 
if ($ReportType -eq "Yearly") {
    $IncrementType = "Years"
}
$IncrementBackValue = -1

## Connect to the target SQL Server and run the query to refresh data
$cstr = "Server=SERVERNAME\INSTANCENAME;Database=Logging;Trusted_Connection=True;"
$cn = new-object system.data.SqlClient.SqlConnection($cstr);
LogWrite "Connecting to SQL"
$cn.Open()

#Query the first date from using the built-in function  
$dateQuery = "SELECT [dbo].[ufn_getIncrementBackDate]('$DateString', '${IncrementType}', ${IncrementBackValue}) as [StartDate]"

$cmd1 = New-Object System.Data.SqlClient.SqlCommand($dateQuery, $cn)
$DateDS = $cmd1.ExecuteReader();
while($DateDS.Read()){
    $StartDate = $DateDS.GetDateTime(0)
}
$DateDS.Close()

#Copy isn't needed Open the template and then at the End do a save as


LogWrite "Opening Excel workbook..."
# Open the Excel document and pull in the 'Data' worksheet
$Excel = New-Object -Com Excel.Application
$Workbook = $Excel.Workbooks.Open($TemplateFilePath) 
$page = 'Data'
$ws = $Workbook.worksheets | where-object {$_.Name -eq $page}
# Delete the current contents of the page
$ws.Cells.Clear() | Out-Null

LogWrite "Generating Report Data..."
#Prepare adapter objects for reading info into Excel
$ds = new-object "System.Data.DataSet" "dsProductData"
$q = "usp_GenerateDatabaseSizeReport @FirstDate='$StartDate'"
$da = new-object "System.Data.SqlClient.SqlDataAdapter" ($q, $cn)
$da.Fill($ds) | Out-Null
$dtProduct = $ds.Tables[0]

# Set variables for the worksheet cells, and for navigation
$cells=$ws.Cells
$row=1
$col=1
$MaxCol = 1
$MaxRow = 1

LogWrite "Populating Data worksheet.."
#Fill Headers
foreach($column in $dtProduct.Columns){
    $cells.item($row, $col) = $column.ColumnName
    if ($col -gt $MaxCol){
        $MaxCol = $col
    }
    $col++
}

# Add the results from the DataTable object to the worksheet
foreach($dataRow in $dtProduct){
    $row++
    $col = 1
    foreach($column in $dtProduct.Columns)
    {
        if ($col -eq 1){
            $cells.item($row, $col) = $dataRow[$column.ColumnName].ToString()
        } else {
            $cells.item($row, $col) = $dataRow[$column.ColumnName].ToString()
        }
        $col++
    }
    if ($row -gt $MaxRow){
        $MaxRow = $row
    }
}

LogWrite "Finished populating Data..."

# Set the width of the columns automatically
$ws.columns.item("A:Z").EntireColumn.AutoFit() | out-null
#Format Date Column
$ws.Range("A2:A1000").NumberFormat ="m/d/yyyy"

#Create the Line Chart's 
$ColumnLetter = "A"


if ($MaxCol -eq 1) { $ColumnLetter = "A" }
if ($MaxCol -eq 2) { $ColumnLetter = "B" }
if ($MaxCol -eq 3) { $ColumnLetter = "C" }
if ($MaxCol -eq 4) { $ColumnLetter = "D" }
if ($MaxCol -eq 5) { $ColumnLetter = "E" }
if ($MaxCol -eq 6) { $ColumnLetter = "F" }
if ($MaxCol -eq 7) { $ColumnLetter = "G" }
if ($MaxCol -eq 8) { $ColumnLetter = "H" }
if ($MaxCol -eq 9) { $ColumnLetter = "I" }
if ($MaxCol -eq 10) { $ColumnLetter = "J" }
if ($MaxCol -eq 11) { $ColumnLetter = "K" }
if ($MaxCol -eq 12) { $ColumnLetter = "L" }
if ($MaxCol -eq 13) { $ColumnLetter = "M" }
if ($MaxCol -eq 14) { $ColumnLetter = "N" }
if ($MaxCol -eq 15) { $ColumnLetter = "O" }
if ($MaxCol -eq 16) { $ColumnLetter = "P" }
if ($MaxCol -eq 17) { $ColumnLetter = "Q" }
if ($MaxCol -eq 18) { $ColumnLetter = "R" }
if ($MaxCol -eq 19) { $ColumnLetter = "S" }
if ($MaxCol -eq 20) { $ColumnLetter = "T" }
if ($MaxCol -eq 21) { $ColumnLetter = "U" }
if ($MaxCol -eq 22) { $ColumnLetter = "V" }
if ($MaxCol -eq 23) { $ColumnLetter = "W" }
if ($MaxCol -eq 24) { $ColumnLetter = "X" }
if ($MaxCol -eq 25) { $ColumnLetter = "Y" }
if ($MaxCol -eq 26) { $ColumnLetter = "Z" }


$RangeString = "A1:"+$ColumnLetter
#$RangeString
$RangeString =$RangeString + $MaxRow
#$RangeString
$range = $ws.range($RangeString)

LogWrite "Performing Chart updates."

$page = "Chart"
$ws = $Workbook.worksheets | where-object {$_.Name -eq $page}
foreach($Shape in $ws.Shapes){
    if ($Shape.HasChart){
        $chart = $Shape.Chart
        break


}

if ($chart -ieq $null){
    Write-Host "Can't find chart!!!"
} else {
    $chart.SetSourceData($range)
}


LogWrite "Saving updated copy of Excel workbook."

# Close the workbook and exit Excel
$Workbook.SaveAs($Destination)
$workbook.Close($true)
$excel.quit()

$DestinationFileO = New-Object System.IO.FileInfo($Destination)
$EmailSubject = $DestinationFileO.Name.Replace($DestinationFileO.Extension, "")

LogWrite "Sending Excel Workbook via email."

#Send an email to operator with report
$SendEmailCmdText ="exec msdb..sp_send_dbmail @profile_name='GMail'
    , @recipients = '${SendReportTo}'
    , @subject = '${EmailSubject}'
    , @body = 'Attached is the $EmailSubject for database server [HOMEGROWNSERVER\TFSSQL].'
    , @file_attachments = '${Destination}'
    --, @query_result_header = 1
    --, @query_result_separator=','
    --, @query_result_width = 32767
    --, @append_query_error = 1
    --, @query_result_no_padding = 1"



$cmd2 = New-Object System.Data.SqlClient.SqlCommand($SendEmailCmdText, $cn)
$cmd2.ExecuteNonQuery()
$cn.Close()
LogWrite "Process Complete"

It might be a credentials problem, which account did you set up on the scheduled task? The same than the one used to execute the script manually? — Chris
– Chris, Commented Mar 10, 2015 at 13:47
I am using the same "Admin" account to execute the scheduled task as I am to test the script, and have "run whether user is logged on or not" selected, task has configured for "Windows 7, Windows Server 2008 R2" selected though I have tried the other "Configure for" options with no success — MBulava
– MBulava, Commented Mar 10, 2015 at 13:55
The next guess would be that the D: drive is not mapped when the automated task runs. — EBGreen
– EBGreen, Commented Mar 10, 2015 at 14:03
D is a physical drive.. it's there and it's where the log file is written to as well. — MBulava
– MBulava, Commented Mar 10, 2015 at 14:08

Jan Chrbolka · Accepted Answer · 2015-03-12 03:29:26Z

2

Your most likely problem is that opening Excel requires an interactive session.

$Excel = New-Object -Com Excel.Application
$Workbook = $Excel.Workbooks.Open($TemplateFilePath)

These lines will fail when you run the script as a scheduled job. As far as I know; in Windows 2012 R2; the only way to get this to work, is to set the scheduled job to “Run only when the user is logged on” and leave the user logged on.

Sorry about the non-answer, but I have not found a workaround for this yet.

answered Mar 12, 2015 at 3:29

Jan Chrbolka

4,4842 gold badges31 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

js2010 Over a year ago

How about using the importexcel module?

Collectives™ on Stack Overflow

PowerShell script via scheduled task not working

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related