Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Powershell error - Ok in ISE , fails in Agent job


Powershell error - Ok in ISE , fails in Agent job

Author
Message
MickyD
MickyD
SSC Veteran
SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)

Group: General Forum Members
Points: 251 Visits: 1472
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.
Gazareth
Gazareth
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2855 Visits: 5304
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
MickyD
MickyD
SSC Veteran
SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)

Group: General Forum Members
Points: 251 Visits: 1472
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
Gazareth
Gazareth
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2855 Visits: 5304
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
MickyD
MickyD
SSC Veteran
SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)

Group: General Forum Members
Points: 251 Visits: 1472
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()
Gazareth
Gazareth
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2855 Visits: 5304
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
Drenlin
Drenlin
SSC Veteran
SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)

Group: General Forum Members
Points: 221 Visits: 165
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.
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8231 Visits: 14368
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
MickyD
MickyD
SSC Veteran
SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)

Group: General Forum Members
Points: 251 Visits: 1472
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search