1

I have the following code, and the stored procedure is used to call several stored procedures. I can run the stored procedure and it will complete without issues in SQL 2012. I am using Python 3.3. 

cnxn = pyodbc.connect('DRIVER={SQL Server};Server=.\SQLEXPRESS;Database=MyDatabase;Trusted_Connection=yes;')
cursor = cnxn.cursor()

cnxn.timeout = 0
cnxn.autocommit = True

cursor.execute("""exec my_SP""")

The python code is executing, I have determined this from inserting numerous prints. I did see the other question regarding python waiting for the SP to finish. I tried adding a 'time.sleep()' after the execute, and varying the time (up to 120 seconds) no change. The stored procedure appears to be partially executing, based on the results. The data suggests that it is even interrupting one of the sub-stored procedures, yet it is fine when the SP is run from query analyzer.

My best guess would be that this is something SQL config related, but I am lost in where to look.

Any thoughts?

sparky
  • 11
  • 1
  • 3

3 Answers3

1

Adding SET NOCOUNT OFF to my proc worked for me.

Hello World
  • 63
  • 1
  • 6
1

I had the same issue and solved it with a combination of setting a locking variable (see answer from Ben Caine in this thread: make python wait for stored procedure to finish executing) and adding

"SET NOCOUNT ON"

after "CREATE PROCEDURE ... AS"

Igge
  • 11
  • 1
0

Just a follow up; I have had limited success using the time features located at the link below, and reducing the level of nesting stored procedures.

At the level that I was calling in the above, there were 4 layers of nested SP's; pyodbc seems to behave a little better when you have 3 layers or less. Doesn't make a lot of sense to me, but it works.

make python wait for stored procedure to finish executing

Any input on the rationale behind this would be greatly appreciated.

Community
  • 1
  • 1
sparky
  • 11
  • 1
  • 3