Error when calling a procedure from pyodbc

Question

This is my first question here. So, I am sorry if it is repeated or the formatting is off. I searched through other questions and the error is common but appears on multiple situations.

I have a 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 am using call instead of exec after reading that ODBC uses call for executing 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

@FlipperPA - Actually, the {CALL ...} syntax is a standard way of calling stored procedures via ODBC (and JDBC), and it did just work for me when I tried it (with pyodbc 3.0.7 and SQL Server Native Client 10.0). — Gord Thompson
– Gord Thompson, Commented Apr 29, 2016 at 17:34
Huh, I had run into errors with that years ago, but now that I think about it, it might have been through Perl's DBD::ODBC. Thanks Gord! — FlipperPA
– FlipperPA, Commented Apr 29, 2016 at 18:33

Cesar Hernandez · Accepted Answer · 2016-04-29 17:56:39Z

2

In case someone is having the same issue. I was able to find out what the problems was. When you open a connection with DSN, the autocommit is set to False. For some reason, this should be True for the code to work (this depends largely on what I was doing on MSSQL).

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

This runs well!

answered Apr 29, 2016 at 17:56

Cesar Hernandez

1351 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

FlipperPA · Accepted Answer · 2016-04-29 01:04:05Z

1

Here are two examples on how you can execute a stored proc in MS SQL Server through pyodbc:

Passing a NULL, and the VARCHAR 'tallen' as positional parameter variables:

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

Passing two VARCHAR values as named parameter variables:

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

Then to loop through the returned data:

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

Good luck!

answered Apr 29, 2016 at 1:04

FlipperPA

14.4k5 gold badges46 silver badges76 bronze badges

Comments

pradsav · Accepted Answer · 2017-05-24 21:28:43Z

0

I had issue with executing the Stored procedure using the SQL server 2008. The first step I did is to go to control panel > Administrative tools > Data Sources (ODBC) > add the driver

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

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}")

answered May 24, 2017 at 21:28

pradsav

711 silver badge4 bronze badges

Collectives™ on Stack Overflow

Error when calling a procedure from pyodbc

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related