Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Powershell error - Ok in ISE , fails in Agent job Expand / Collapse
Author
Message
Posted Monday, July 22, 2013 3:06 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 3:21 PM
Points: 229, Visits: 1,154
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.
Post #1475900
Posted Monday, July 22, 2013 3:32 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, September 18, 2014 10:33 AM
Points: 1,956, Visits: 3,292
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
Post #1475907
Posted Monday, July 22, 2013 4:06 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 3:21 PM
Points: 229, Visits: 1,154
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
Post #1475919
Posted Monday, July 22, 2013 4:58 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, September 18, 2014 10:33 AM
Points: 1,956, Visits: 3,292
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
Post #1475938
Posted Monday, July 22, 2013 5:05 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 3:21 PM
Points: 229, Visits: 1,154
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()


Post #1475941
Posted Monday, July 22, 2013 7:13 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, September 18, 2014 10:33 AM
Points: 1,956, Visits: 3,292
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
Post #1476001
Posted Tuesday, July 23, 2013 2:20 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 1, 2014 8:14 AM
Points: 177, Visits: 144
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.
Post #1476404
Posted Wednesday, July 24, 2013 6:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:27 PM
Points: 7,107, Visits: 12,657
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
Post #1477021
Posted Thursday, July 25, 2013 4:23 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 3:21 PM
Points: 229, Visits: 1,154
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
Post #1477425
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse