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?

score 8 · Accepted Answer · answered Apr 29 '16 at 02:46

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)

FlipperPA · Answer 2 · 2018-12-10T16:13:10.710

It looks like you've skipped making a cursor, so you need to do that, then fetch the results. Try this:

import pyodbc

connection = pyodbc.connect('DRIVER={SQL Server}; SERVER=ServerName; PORT=1433;DATABASE=dbname;UID=%s;PWD=%s'  % (username, password))

cursor = connection.cursor()
cursor.execute("EXECUTE msdb.dbo.sp_start_job 'TestSP'")

rows = cursor.fetchall()
for row in rows:
    # Do stuff
    print(row)

How to return errors from PYODBC

2 Answers2