Error calling procedure from pyodbc

Question

This is my first question. Therefore, sorry if it repeats or formatting is disabled. I looked at other questions, and the error is common, but appears in several situations.

I have very simple python code where I want to execute a procedure in MSSQL from pyodbc.

import pyodbc conn = pyodbc.connect(r'DSN=myDSN') cursor = conn.cursor() query = r'{call myproc}' cursor.execute(query) 

I use a call instead of exec after reading when ODBC uses a call to execute procedures in MSSQL.

The error I am getting is the following:

 Traceback (most recent call last): File "myscript.py", line 26, in <module> cursor.execute(query) pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]The current transaction has aborted, and any pending changes have been rolled back. Cause: A transaction in a rollback-only state was not explicitly rolled back before a DDL, DML or SELECT statement. (111233) (SQLExecDirectW)') 

thanks for the help

Answer 1

If anyone has the same problem. I managed to find out what the problems were. When you open a DSN connection, False is set for auto-exchange. For some reason, it must be True for the code to work (it depends a lot on what I did in MSSQL).

 import pyodbc conn = pyodbc.connect(r'DSN=myDSN', autocommit=True) cursor = conn.cursor() query = r'{call myproc}' cursor.execute(query) 

It works well!

Answer 2

Here are two examples of how you can execute a stored procedure in MS SQL Server via pyodbc:

Passing NULL and VARCHAR 'tallen' as positional parameter variables:

 cursor.execute('EXEC usp_get_user_data ?, ?', None, 'flipperpa') 

Passing two VARCHAR values ​​as parameter name variables:

 cursor.execute('EXEC usp_get_user_data @user_full_name = ?, @user_username = ?', 'flip', 'flipperpa') 

Then, to iterate over the returned data:

 rows = cursor.fetchall() for row in rows: # Do stuff print(row.user_id) 

Good luck

Answer 3

I had a problem executing a stored procedure using SQL Server 2008. The first step I did was go to Control Panel> Administrative Tools> Data Sources (ODBC)> Add Driver

The only change I made in my Python code is to use "{call procdev_2017}". I got an error when I tried to use exec instead of calling

 import pyodbc conn = pyodbc.connect(driver='{SQL Server Native Client 11.0}', server='XXXXXXX', database='XXX', trusted_connection='yes', autocommit=True) bepcur = conn.cursor() ipcur.execute("{call procdev_2017}")