stdout pymssql to variable

Question

Im trying to retrieve the result form running

import pymssql
conn = pymssql.connect(server='IP', user='domain\user', password='PSWD', tds_version='8.0')
cursor = conn.cursor()
cursor.execute("EXEC msdb.dbo.sp_start_job @job_name = 'jobname'")

when it add the job to the cue to process it wont return anything, but when the job wasn't runned it will return stuff like in a case for testing

Traceback (most recent call last):
  File "shared/python3", line 85, in <module>
    cursor.execute("EXEC msdb.dbo.sp_start_job @job_name = ''")
  File "pymssql.pyx", line 467, in pymssql.Cursor.execute (pymssql.c:7533)
pymssql.OperationalError: (14262, "The specified @job_name ('') does not exist.DB-Lib error message 14262, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\n")

In this case the error is pointing out tha the Job_name doesn't exist. What I want to do is put the result on a string variable that I can pars for error control...

I have tried this:

import sys

# Store the reference, in case you want to show things again in standard output
old_stdout = sys.stdout

# This variable will store everything that is sent to the standard output
result = StringIO()
sys.stdout = result

# Here we can call anything we like, like external modules, and everything that they will send to standard output will be stored on "result"
cursor.execute("EXEC msdb.dbo.sp_start_job @job_name = 'jobname'")

# Redirect again the std output to screen
sys.stdout = old_stdout

# Then, get the stdout like a string and process it!
result_string = result.getvalue()
process_string(result_string)

link. but couldn't get it to work.

Answer 1

You are seeing the traceback because you are not handling the exception that occurs when the job name does not exist. If you want to capture the error message you can simply catch the exception. As a general-purpose example, instead of just doing

crsr.execute(sql)

you could do

try:
    crsr.execute(sql)
except Exception as e:
    (error_code, error_message) = e

and then use the error_code and error_message values to write them to a log file, or spit them out to the console, or whatever.