Execution of Powershell script fails on SQL Agent

Question

Execution of Powershell script fails on SQL Agent

Kai Herrmann 21

Hi,

I have developed a powershell script that calls an executable with a time limit of 15 minutes

To implement this I downloaded the package for the Start_threadjob from the powershell library. The script runs as expected when executing the script on the server from powershell or as compiled exe file

Code:

   # execute program as thread job with a maximum run time of 15 minutes.   
     
   $ESDjob = Start-ThreadJob { & $using:prgPath @using:prgArgs}

But when I run this as a step of a SQL agent job, the error below is thrown:

The term 'Start-ThreadJob' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

I have other PS modules that I downloaded that can be executed as SQL agent. What could cause this issue?

0 comments

Answer accepted by question author

Olaf Helper 47,621

The term 'Start-ThreadJob' is not recognized as the name of a cmdlet,

It's because it's not a default CmdLet available in a SQL Server-Agent job; you have to import the CmdLet in your script first.

But why, the job should already run as it's own thread, don't make much sense to use additional threading?

Kai Herrmann 21 Reputation points

2022-09-07T14:11:32.117+00:00

Thanks - I did not realize that when I installed the module it put it into my profile and not in the path of the SQL server - I thought all modules are strored in the same place, but when I checked I saw that there were three locations where modules are located.
the reason I use a separate thread for the job is to time the execution. The job starts a script on an AS400 system which typically takes 15 min to complete and creates an exit code, but in some cases it runs indefinetly, so I need to catch it after some allowed run time and notify the operator of the IBM system to kill the job there and check resources to see why this job did not complete in the usual time. Any other suggestion?

1 additional answer

Your answer

Kai Herrmann 21 Reputation points

2022-09-07T14:11:32.117+00:00

Thanks - I did not realize that when I installed the module it put it into my profile and not in the path of the SQL server - I thought all modules are strored in the same place, but when I checked I saw that there were three locations where modules are located.
the reason I use a separate thread for the job is to time the execution. The job starts a script on an AS400 system which typically takes 15 min to complete and creates an exit code, but in some cases it runs indefinetly, so I need to catch it after some allowed run time and notify the operator of the IBM system to kill the job there and check resources to see why this job did not complete in the usual time. Any other suggestion?

Answer 1

PandaPan-MSFT 1,931

Hi @Kai Herrmann ,
Maybe you can try to update your version to 1.11.0. I find one article that may give you some help.https://github.com/jdhitsolutions/PSCalendar/issues/19

Best regards
Jong

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment"

0 comments

Share via

Execution of Powershell script fails on SQL Agent

1 additional answer

Your answer