locked
run python script from sql server job and handle python error RRS feed

  • Question

  • I have a python script that automates a lot of tasks and uses libraries that aren't available outside of python. What I would like to do is create a SQL Server Job that calls the python script and passes in parameters for each record that meets the search criteria. However, I also need the Job to respond correctly if the script encounters an error. One way I was thinking of handling this was to have the python script exit, using sys.exit() of sys.exit(0) for no error and sys.exit(1) for error, if it encounters an error. But I can't figure out how to get the Job to catch if the script had an error, sys.exit() does exit the script as an error.

    The only suggestions that I've gotten so far is for the Job to pass in the path to a temporary log, have the python script log if it was successful or not, and then have the Job read that log file and act accordingly. But this seems like a rather odd workaround. Are there any ways to have the Job catch sys.exit() or any other approaches that I can use for this?

    We could have the python script connect to the database to handle the record update but I am really trying to avoid storing a username and password in a connection string in the python script. If there's a way to give the script access to the database based on being called from the Job I'm open to that but I can't figure out how to do that either.

    I would write it all in the Job but some of the libraries that I'm using are specific to programs that they're automating and are only available for python. If there's a way to write a Job using python I could give that a try.


    • Edited by shepa_006 Friday, October 17, 2014 3:05 AM
    Friday, October 17, 2014 3:00 AM

Answers

  • I'd assume that your Python code can return an integer which can be caught by a CmdExec jobstep using ERRORLEVEL (or similar in powershell, if you wish). Agent considers a CmdExec jobsteo success if ERRORLEVEL (returncode) is 0, else failed - by default.

    Tibor Karaszi, SQL Server MVP | web | blog

    Friday, October 17, 2014 11:01 AM