0

I am not proficient in Python but I have written a python code that executes a stored procedure (SQL server) which within it contains multiple stored procedures therefore it usually takes 5 mins or so to run on SSMS.

I can see the stored procedure runs halfway through without error when I run the Python code which makes me think that somehow it needs more time to execute when coding in python.

I found other posts where people suggested subprocess but I don't know how to code this. Below is an example of a (not mine) python code to execute the stored procedure.

mydb_lock = pyodbc.connect('Driver={SQL Server Native Client 11.0};'
                       'Server=localhost;'
                       'Database=InterelRMS;'
                       'Trusted_Connection=yes;'
                       'MARS_Connection=yes;'
                       'user=sa;'
                       'password=Passw0rd;')

mycursor_lock = mydb_lock.cursor()
sql_nodes = "Exec IVRP_Nodes"
mycursor_lock.execute(sql_nodes)
mydb_lock.commit()

How can I edit the above code to use the subprocess? Is the subprocess the right choice? Any other method you can suggest?

Many thanks.

Python 2.7 and 3 SQL Server

UPDATE 04/04/2022:

@AlwaysLearning, I tried

 NEWcnxn   = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password+';Connection Timeout=0')

But there was no change. What I noticed is that to check how much of the code it executes, I inserted the following two lines of code right after each other somewhere in the nested procedure where I thought the SP stopped.

INSERT INTO  CheckTable (OrgID,Stage,Created) VALUES(@OrgID,2.5331,getdate()) 
INSERT INTO  CheckTable (OrgID,Stage,Created) VALUES(@OrgID,2.5332,getdate())

Only the first query is completed. I use Azure DB if that helps.

UPDATE 05/04/2022:

I tried what @AlwaysLearning suggested, after my connection, I added, NEWconxn.timeout=4000 and it's working now

MILAD TOOLABI
  • 11
  • 6
  • Does this answer your question? [make python wait for stored procedure to finish executing](https://stackoverflow.com/questions/24458430/make-python-wait-for-stored-procedure-to-finish-executing). I'd recommend to test solution provided in [this](https://stackoverflow.com/a/68535140/10824407) particular answer. – Olvin Roght Mar 28 '22 at 09:22
  • Is there some reason you're still using SQL Server Native Client 11.0 (SNAC11)? It's deprecated and [has not been updated since SQL Server 2012](https://learn.microsoft.com/en-us/sql/relational-databases/native-client/sql-server-native-client) was a thing. And Python 2.7? – AlwaysLearning Mar 28 '22 at 09:55
  • AlwaysLearning, above was just an example and the code was not mine. Hi Olvin Rough. I have already tried the loop in the link you kindly shared. The creation of the new connection certainly helped but now it runs a bit more before it stops. Can the 'command timeout' be an issue since my queries take a long time to run? – MILAD TOOLABI Mar 28 '22 at 14:47
  • The `Connection` object returned from `pyodbc.connect()` has a [`timeout`](https://github.com/mkleehammer/pyodbc/wiki/Connection#timeout) attribute for setting command/query/statement timeouts in seconds. Have you tried setting it yet? – AlwaysLearning Apr 01 '22 at 11:41
  • @AlwaysLearning many thanks for your input, I will try this and will get back to you. – MILAD TOOLABI Apr 04 '22 at 10:31
  • @AlwaysLearning please find my updated response in the question section. Any comments? – MILAD TOOLABI Apr 04 '22 at 20:15
  • `Connection Timeout` is not a standard ODBC [Connection String Keyword](https://learn.microsoft.com/en-us/sql/connect/odbc/dsn-connection-string-attribute). pyodbc uses `SQL_ATTR_CONNECTION_TIMEOUT` as per [issue #106](https://github.com/mkleehammer/pyodbc/issues/106) to influence the connection timeout - which is how long Python will wait for a new connection to be established to the database. Your question, however, is about controlling how long to wait for a query to execute... so did you try the `timeout` attribute on the `Connection` object returned from `pyodbc.connect()`? – AlwaysLearning Apr 04 '22 at 22:05
  • @AlwaysLearning, I tried, `NEWcnxn.timeout=4000`, and it's working now (I had to wait for a bot for the tables to be updated). Thank you so very much. if you make this an answer then I can accept it as answered. – MILAD TOOLABI Apr 05 '22 at 18:24

1 Answers1

0

I tried what @AlwaysLearning suggested, after my connection, I added, NEWconxn.timeout=4000 and it's working now. Many thanks.

MILAD TOOLABI
  • 11
  • 6