CODEWITHSUNDEEP
pythonpyodbc
mikebmassey
mikebmassey

Reputation: 8604

How to return errors from PYODBC

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?

Upvotes: 7

Views: 14177

Answers (2)

FlipperPA
FlipperPA

Reputation: 14361

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)

Upvotes: 2

Parfait
Parfait

Reputation: 107767

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)

Upvotes: 8

Related Questions