July 22, 2013 at 3:06 am
Morning all.
I am trying to run script to get Translog details. The script works fine in ISE and produces report as expected. Produces excel report etc.
When it runs in an agent job it fails with the following error:
Executed as user: XXXXXX. A job step received an error at line 56 in a PowerShell script.
The corresponding line is '$logInfo = $db.LogFiles | Select Name, FileName, Size,UsedSpace'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'The following exception was thrown when trying to enumerate the collection: "An exception occurred while executing a Transact-SQL statement or batch.". '. Process Exit Code -1. The step failed.
This is part of that script.
$intRow++
$logInfo = $db.LogFiles | Select Name, FileName, Size, UsedSpace
If ($loginfo.UsedSpace / $loginfo.Size * 100 -gt 50)
{
$dbname = $db.Name
$Sheet.Cells.Item($intRow,2) = $dbname
$Sheet.Cells.Item($intRow,3) = ($logInfo.Size / 1024)
$Sheet.Cells.Item($intRow,4) = ($logInfo.UsedSpace / 1024)
$Sheet.Cells.Item($intRow,5) = ($loginfo.UsedSpace / $loginfo.Size * 100)
$Sheet.Cells.Item($intRow,5).Interior.ColorIndex = 3
}
else
{
$dbname = $db.Name
$Sheet.Cells.Item($intRow,2) = $dbname
$Sheet.Cells.Item($intRow,3) = ($logInfo.Size / 1024)
$Sheet.Cells.Item($intRow,4) = ($logInfo.UsedSpace / 1024)
$Sheet.Cells.Item($intRow,5) = ($loginfo.UsedSpace / $loginfo.Size * 100)
$Sheet.Cells.Item($intRow,5).Interior.ColorIndex = 14
}
}
I am doing anything wrong there ?
Confused over how it works fine in ISE but fails as a SQL agent job.
Any advice appreciated.
July 22, 2013 at 3:32 am
Bit of a guess, but permissions issue? Presumably the ISE is running under your credentials whilst the SQL Agent one will be the SQL Agent account.
Is the job targeting the local server or a remote one?
Cheers
Gaz
July 22, 2013 at 4:06 am
Gazareth (7/22/2013)
Bit of a guess, but permissions issue? Presumably the ISE is running under your credentials whilst the SQL Agent one will be the SQL Agent account.Is the job targeting the local server or a remote one?
Cheers
Gaz
Hi Gaz
Don't think it is permission. Other Agent jbs run fine. Agent runs under my AD account for testing purposes.
Job is targeting remote servers but I have access there , and again access to these remote servers works for other jobs.
cheers
July 22, 2013 at 4:58 am
No worries.
Can you give any more of the script? Particularly how $db is populated.
Is the step in the agent job a powershell step, command line, or something else?
Thanks
July 22, 2013 at 5:05 am
Gazareth (7/22/2013)
No worries.Can you give any more of the script? Particularly how $db is populated.
Is the step in the agent job a powershell step, command line, or something else?
Thanks
Hi Gaz
thanks for help , here is the script
Executed from SQL agent job.
Any help or improvements appreciated.
#Create a new Excel object using COM
$path = "D:\PowerShell\Approval_Instance_TransLogs.xlsx"
if((Test-Path -Path $path))
{
Remove-Item "D:\PowerShell\Approval_Instance_TransLogs.xlsx"
}
#Create a new Excel object using COM
$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
foreach ($instance in get-content "D:\PowerShell\ApprovalSQLServers.txt")
{
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance
#This sets the connection to mixed-mode authentication
$s.ConnectionContext.LoginSecure=$false;
#This sets the login name
$s.ConnectionContext.set_Login("XXXXXXX");
#This sets the password
$s.ConnectionContext.set_Password("??????")
$dbs=$s.Databases
$intRow++
#Create column headers
$Sheet.Cells.Item($intRow,1) = "Server: $s"
$Sheet.Cells.Item($intRow,1).Font.Size = 12
$Sheet.Cells.Item($intRow,1).Font.Bold = $True
$intRow++
$Sheet.Cells.Item($intRow,2) = "Database"
$Sheet.Cells.Item($intRow,2).Font.Bold = $True
$Sheet.Cells.Item($intRow,3) = "Log Size (MB)"
$Sheet.Cells.Item($intRow,3).Font.Bold = $True
$Sheet.Cells.Item($intRow,4) = "Log Used Space (MB)"
$Sheet.Cells.Item($intRow,4).Font.Bold = $True
$Sheet.Cells.Item($intRow,5) = "PercentageUsed(%)"
$Sheet.Cells.Item($intRow,5).Font.Bold = $True
foreach ($db in $dbs)
{
# write-host $fg.files | select name
$intRow++
$logInfo = $db.LogFiles | Select Name, FileName, Size,UsedSpace
If ($loginfo.UsedSpace / $loginfo.Size * 100 -gt 50 )
{
$dbname = $db.Name
$Sheet.Cells.Item($intRow,2) = $dbname
$Sheet.Cells.Item($intRow,3) = ($logInfo.Size / 1024)
$Sheet.Cells.Item($intRow,4) = ($logInfo.UsedSpace / 1024)
$Sheet.Cells.Item($intRow,5) = ($loginfo.UsedSpace / $loginfo.Size * 100)
$Sheet.Cells.Item($intRow,5).Interior.ColorIndex = 3
}
else
{
$dbname = $db.Name
$Sheet.Cells.Item($intRow,2) = $dbname
$Sheet.Cells.Item($intRow,3) = ($logInfo.Size / 1024)
$Sheet.Cells.Item($intRow,4) = ($logInfo.UsedSpace / 1024)
$Sheet.Cells.Item($intRow,5) = ($loginfo.UsedSpace / $loginfo.Size * 100)
$Sheet.Cells.Item($intRow,5).Interior.ColorIndex = 14
}
}
$intRow++
}
$Sheet.UsedRange.EntireColumn.AutoFit()
$Excel.SaveAs("D:\PowerShell\Approval_Instance_TransLogs.xlsx")
#Close workbook
$Excel.Close()
#Exit Excel
$Excel.Quit()
July 22, 2013 at 7:13 am
Thanks Micky.
I'd still go back to permissions - the only way I've been able to get the same error is if the user doesn't have the rights it needs. I think it needs access to each database but only the public role inside each (i.e. the sys.database_files view, but that's unconfirmed, I'm not sure what SMO uses to get the data).
I notice you're setting the user/password in the script so it'll be that rather than your own credentials.
Thought it might be a server not using mixed mode but that gives a different error.
As far as ISE/agent difference goes, I haven't been able to reproduce & I can't explain it.
Only guess I have is that the ISE can reuse variables' contents between executions but I'm not sure that applies here.
Cheers
Gaz
July 23, 2013 at 2:20 am
Just a thought (I am still learning with Powershell and SQL).
When you run the script in the ISE, are you logged into Windows using an account that is also an SQL Administrator?
It may be the mixed mode authentication is failing but the Windows authentication still allows the script to run.
When you then run this as the agent, it can't use the Windows authentication.
Then again I may be completely wrong.
July 24, 2013 at 6:09 am
You cannot send output to the Out-Host when using a PowerShell step type. Long story, but PowerShell steps in Agent 2008 are not full shell. In Agent 2012 the experience has improved significantly. Instead of using the PowerShell step type, save your code as a ps1 file and execute it using PowerShell.exe within a CmdExec step type.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 25, 2013 at 4:23 am
opc.three (7/24/2013)
You cannot send output to the Out-Host when using a PowerShell step type. Long story, but PowerShell steps in Agent 2008 are not full shell. In Agent 2012 the experience has improved significantly. Instead of using the PowerShell step type, save your code as a ps1 file and execute it using PowerShell.exe within a CmdExec step type.
thanks for update
Noticed that my ps script runs, but does produce errors.
Will work on getting those resolved , then trying again by agent.
cheers
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply