question

Gehima2016 avatar image
Gehima2016 asked

Powershell script sql agent job

Hi everyone, I have a powershell script which is scheduled to run as a job in sql server. The job executes successfully but it doesn't records on the table in sql server table and also it doesn't log file either. Can someone please point me in the right directly
scriptjobpowershellagent
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Gehima2016 This means that there are errors in the powershell script which are not bubbled up to the job. In other words, the error occurs inside of the script, but is not reported back, and therefore, the job completes reporting success. This behaviour is by design, it is exactly what is supposed to happen. It is up to the author of the powershell script to handle errors differently and allow them to bubble up if needed. If the same script executes and does what it needs to do when executed from the PS window then the problem will be most likely related to permissions of the account under which the SQL Agent is running. It is impossible to tell exactly what went wrong though until the way the errors are handled is modified. Just modify the powershell script to bubble up the errors, and this will point you in the right direction.
0 Likes 0 ·
Just set `$ErrorActionPreference = stop` at the top of your script ...that will cause any error in the script to bubble up to the Agent history.
0 Likes 0 ·
@oleg I would also not state it is by design. The PS step in SQL Agent has many issues, one of which is changing the default behavior of errors in PS. The author of any PS script should not have to "change" the way they handle errors just because we try to run it via SQL Agent. As well, the main issue with PS scripts not running in SQL Agent is due to the environment the step runs under...sqlps.exe. It is executing with the PS host that MS wrote, which is basically crap and not a true PS environment. So if you want to verify your script will run, open up `sqlps.exe` and then run your script. You are in the context of the provider in `sqlps.exe` and the PS step of SQL Agent jobs.
0 Likes 0 ·
@Shawn_Melton Sorry about that. What I meant to say was that if one were to contact MS inquiring about the issue, their answer would be that it is by design :( Setting $ErrorActionPreference = stop as you suggested and possibly trapping the error in the catch block, handling it if needed and then re-throwing it back before exiting with non-zero code (Environment.Exit(1)) would probably be their suggestion too. I had to experience the bite of that behaviour once as well when I scheduled the job for the first time and thought that all is well next morning (no email from agent) just to find out later that no work was done.
0 Likes 0 ·

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.