CODEWITHSUNDEEP
sql-serverpython-3.xstored-procedurespyodbc
Sanjay Yadav
Sanjay Yadav

Reputation: 799

MSSQL Stored procedure exits without full execution using pyodbc

I am using executing MSSQL stored procedure using python(3.6)'s pyodbc module. In case if my stored procedure is getting more than 4 minutes of execution time, it silently exits from there without any error.

I sets up remote connection time out to zero (which means infinite timeout).

Problem is that it is not throwing any error/exceptions, so I am not able to trace it.

This problem is happening only when stored procedure is taking long execution time.

Why this is happening ? Is there any limitation from pyodbc side ? Or any limitation from mssql server end ?

Code snippet is like below:-

conn = pyodbc.connect("Driver={SQL Server Native Client 10.0};Server=myserver;Databse=mydb;Trusted_Connection=True")
sql = """
    declare @cnt int=1
    while @cnt < 100
    begin
        print('dummy message')
        print('dummy message')
        print('dummy message')
        print('dummy message')
        print('dummy message')
        print('dummy message')
        print('dummy message')
        print('dummy message')
        print('dummy message')
        print('dummy message')
        print('dummy message')
        print('dummy message')
        print('dummy message')


        insert into table1 values (@cnt)
        set @cnt = @cnt + 1
    end
    "
result = conn.execute(sql)
print(result.fetchall())

If there are many statements are getting executed or many print statements are there then stored procedure is simply exiting without any error. print/update/insert statements can be avoided by setting SET NOCOUNT ON which I already tried.

Upvotes: 0

Views: 537

Answers (1)

Helpful Guru
Helpful Guru

Reputation: 46

Without seeing the code it's hard to give you targeted advice, so this is a very generic answer, but hopefully it helps point you in the right direction.

Separation

When you have an unexplained problem while two pieces are working together (MS SQL Server and pyodbc) it can be difficult to work out which piece has the issue. I'd suggest you focus on getting more information from the MS SQL Server part.

Get more information from procedure

  1. Add error checking to the MS SQL Server procedure so any errors encountered are logged in a table - there may be an error thrown that's not being picked up by you pyodbc code for some reason.
  2. Add some kind of logging from within the procedure, so you can see exactly where it got up to, and how long it took to do each step.

I'd also suggest you use SQL Server profiler if you're familiar with it, but that might not be an option if the issue can't be easily repeated in a test environment (if you leave that running for too long you get too much info to work with).

Error handling in calling code

If everything checks out in the database part, try and get more information from the calling code. Again, add more error handling and debugging code to find out exactly where the point of failure is. To confirm your code is setup correctly to handle errors deliberately code an error in your stored procedure (divide by zero is a nice easy one to create) and make sure the calling code handles the error. You might find you are checking the wrong return codes or something and that's why your calling code isn't detecting any issues.

Timeouts

There is more than one time out setting in MS SQL Server - they can be set via code or connection string. Connection Timeout setting: 15 Seconds Command/Query Timeout Setting: 30 Seconds But that's just the DB timeouts. Your application could also have a timeout. For example, HTML responses have timeouts to handle idle connections (408 Request Timeout), some servers kill the connection without sending any message.

Upvotes: 2

Related Questions