SQL Stored Procedures not finishing when called from Python

Question

I'm trying to call a stored procedure in my MSSQL database from a python script, but it does not run completely when called via python. This procedure consolidates transaction data into hour/daily blocks in a single table which is later grabbed by the python script. If I run the procedure in SQL studio, it completes just fine.

When I run it via my script, it gets cut short about 2/3's of the way through. Currently I found a work around, by making the program sleep for 10 seconds before moving on to the next SQL statement, however this is not time efficient and unreliable as some procedures may not finish in that time. I'm looking for a more elegant way to implement this.

Current Code:

cursor.execute("execute mySP")
time.sleep(10)
cursor.commit()

The most related article I can find to my issue is here: make python wait for stored procedure to finish executing

I tried the solution using Tornado and I/O generators, but ran into the same issue as listed in the article, that was never resolved. I also tried the accepted solution to set a runningstatus field in the database by my stored procedures. At the beginnning of my SP Status is updated to 1 in RunningStatus, and when the SP finished Status is updated to 0 in RunningStatus. Then I implemented the following python code:

    conn=pyodbc_connect(conn_str)
    cursor=conn.cursor()
    sconn=pyodbc_connect(conn_str)
    scursor=sconn.cursor()

    cursor.execute("execute mySP")
    cursor.commit()
    while 1:
        q=scursor.execute("SELECT Status FROM RunningStatus").fetchone() 
        if(q[0]==0):
            break

When I implement this, the same problem happens as before with my storedprocedure finishing executing prior to it actually being complete. If I eliminate my cursor.commit(), as follows, I end up with the connection just hanging indefinitely until I kill the python process.

    conn=pyodbc_connect(conn_str)
    cursor=conn.cursor()
    sconn=pyodbc_connect(conn_str)
    scursor=sconn.cursor()

    cursor.execute("execute mySP")
    while 1:
        q=scursor.execute("SELECT Status FROM RunningStatus").fetchone() 
        if(q[0]==0):
            break

Any assistance in finding a more efficient and reliable way to implement this, as opposed to time.sleep(10) would be appreciated.

A [poster on R also running ODBC to SQL Server](https://stackoverflow.com/q/47249513/1422451) asked nearly same question where stored proc does not complete fully but does in MSSQL. I'll ask like I asked there: Is there a semicolon placed in middle? Are you wrapping entire call in `BEGIN...END`? Do you have `SET NOCOUNT ON`? Better yet, post the TSQL. — Parfait, Nov 13 '17 at 21:46
Was missing BEGIN..END tags. Placed those and it appears to be working correctly now. Thanks — MrDominati0n, Nov 13 '17 at 22:24
I've tested this on a different machine, and with much larger stored procedures, and it is continuing to finish the stored procedure before moving on. — MrDominati0n, Nov 14 '17 at 03:23

score 3 · Accepted Answer · edited Jul 29 '20 at 18:22

As OP found out, inconsistent or imcomplete processing of stored procedures from application layer like Python may be due to straying from best practices of TSQL scripting.

As @AaronBetrand highlights in this Stored Procedures Best Practices Checklist blog, consider the following among other items:

Explicitly and liberally use BEGIN ... END blocks;
Use SET NOCOUNT ON to avoid messages sent to client for every row affected action, possibly interrupting workflow;
Use semicolons for statement terminators.

Example

CREATE PROCEDURE dbo.myStoredProc
AS
BEGIN
   SET NOCOUNT ON;

   SELECT * FROM foo;

   SELECT * FROM bar;
END
GO

SQL Stored Procedures not finishing when called from Python

1 Answers1

Linked