How to return errors from PYODBC

Question

I'm making a connection to SQL Server to execute a stored procedure. What is the correct way to 'poll' the server to determine whether the stored procedure finished running successfully or returned an error if the SP takes longer than 60 seconds / 3600 seconds, etc?

import pyodbc
cnxn = pyodbc.connect('DRIVER={SQL Server}; SERVER=ServerName; PORT=1433;DATABASE=dbname;UID=%s;PWD=%s'  % (username, password))
cnxn.execute("EXECUTE msdb.dbo.sp_start_job 'TestSP'")
<pyodbc.Cursor object at 0x0000000002D6DDB0>

How can I determine the status of the SP?

Answer 1

Consider wrapping the execute in a try/except to catch exceptions (which encompass errors). If no error is raised, execute is assumed to run correctly. Also, use the timeout variable (in seconds) as the database should raise OperationError if timeout occurs.

cnxn = pyodbc.connect('DRIVER={SQL Server}; SERVER=ServerName; PORT=1433; \
                       DATABASE=dbname;UID={0};PWD={1}'.format(username, password))

cnxn.timeout = 60    
cursor = cnxn.cursor()
try:
    cnxn.execute("EXECUTE msdb.dbo.sp_start_job 'TestSP'")
except Exception as e:
    print(e)