Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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 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, 2017 at 21:46
                Was missing BEGIN..END tags. Placed those and it appears to be working correctly now. Thanks
– MrDominati0n
                Nov 13, 2017 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, 2017 at 3:23
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
BEGIN
   SET NOCOUNT ON;
   SELECT * FROM foo;
   SELECT * FROM bar;
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.