July 12, 2013 at 3:09 am
Hi everyone.
I have a powershell script that outputs to .xlsx file.
Works fine in Powershell, no issues.
However when I try to schedule it as an SQL agent job it fail.
Agent account running with permissions to dir where .xlsx to be saved.
Script does shut down Excel when completed.
This is the error.
The error information returned by PowerShell is: 'Exception calling "SaveAs" with "1" argument(s): "SaveAs method of Workbook class failed"
Had a look around but can't seem to find a solution. Any adivse gratefully recieved.
cheers
July 12, 2013 at 4:01 am
Is Excel installed on the maching hosting SQL?
Can you post the PowerShell script and the entire error message?
Joie Andrew
"Since 1982"
July 12, 2013 at 5:51 am
Hi Joie
Yes excel is installed on the machine. I am running SQL 2012 on the machine and Excel is on there as well.
Still learning PS so any advice is welcomed.
This is the script that works fine in PowerGui.
#Create a new Excel object using COM
$path = "Y:\PowerShell\Failedjobs.xlsx"
if((Test-Path -Path $path))
{
Remove-Item "Y:\PowerShell\Failedjobs.xlsx"
}
$Excel = New-Object -ComObject Excel.Application
#$Excel.visible = $True
#Add new workbook
$Excel = $Excel.Workbooks.Add()
#Add new worksheet to workbook
$Sheet = $Excel.Worksheets.Item(1)
#Counter variable for rows
$intRow = 1
#Read thru the contents of the SQL_Servers.txt file
foreach ($instance in get-content "Y:\PowerShell\SQLServers_Live.txt")
{
#Create column headers
$Sheet.Cells.Item($intRow,1) = "SQL INSTANCE NAME:"
$Sheet.Cells.Item($intRow,2) = $instance
$Sheet.Cells.Item($intRow,1).Font.Bold = $True
$Sheet.Cells.Item($intRow,2).Font.Bold = $True
$intRow++
$Sheet.Cells.Item($intRow,1) = "JOB NAME"
$Sheet.Cells.Item($intRow,2) = "LAST RUN OUTCOME"
$Sheet.Cells.Item($intRow,3) = "LAST RUN DATE"
#Format the column headers
for ($col = 1; $col –le 3; $col++)
{
$Sheet.Cells.Item($intRow,$col).Font.Bold = $True
$Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
$Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
}
$intRow++
#######################################################
#This script gets SQL Server Agent job status information using PowerShell
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
# Create an SMO connection to the instance
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance
#This sets the connection to mixed-mode authentication
$srv.ConnectionContext.LoginSecure=$false;
#This sets the login name
$srv.ConnectionContext.set_Login("XXXXXX");
#This sets the password
$srv.ConnectionContext.set_Password("YYYYYY")
$jobs=$srv.JobServer.Jobs | Where-Object {$_.LastRunOutcome -eq 0 -and $_.IsEnabled -eq "TRUE"}
#Formatting using Excel
ForEach ($job in $jobs)
{
If ($job.LastRunOutcome -eq 0)
{
$fgColor = 3
$Sheet.Cells.Item($intRow, 1) = $job.Name
$Sheet.Cells.Item($intRow, 2) = $job.LastRunOutcome.ToString()
$Sheet.Cells.item($intRow, 2).Interior.ColorIndex = $fgColor
$Sheet.Cells.Item($intRow, 3) = $job.LastRunDate
}
else
{
# $fgColor = 10
$Sheet.Cells.Item($intRow,1).Font.Bold = $True
$Sheet.Cells.Item($intRow,1).Font.ColorIndex = 10
$Sheet.Cells.Item($intRow, 1) = "ALL JOBS ARE SUCCESSFULL"
}
$intRow ++
}
$intRow ++
}
$Sheet.UsedRange.EntireColumn.AutoFit()
$Excel.SaveAs("Y:\PowerShell\Failedjobs.xlsx")
#Close workbook
$Excel.Close()
#Exit Excel
$Excel.Quit()
cls
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply