CODEWITHSUNDEEP
pythonsql-server
Carl Zheng
Carl Zheng

Reputation: 718

Run SQL 'Kill' from Python

I always retrieve data from SQL database on Python via pymssql, which may lead too many idle connections. Therefore, I write a SQL procedure to kill idle connections, and it works when I execute on SSMS. However, when I try to execute it on python it fails and raises :

(6115, b'KILL command cannot be used inside user transactions.DB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\n')

The following code is the method to execute SQL procedure from python,

import pymssql

conn = pymssql.connect(server, username, password, dbname)
cursor = conn.cursor()

cursor.execute("exec killconn")

How can I deal with this?

Thank you so much.

Upvotes: 4

Views: 1919

Answers (1)

Philip Cullen
Philip Cullen

Reputation: 21

This may not be the solution to underlying issue of idle connections, but the error from SQL server is caused by pymssql wrapping each connection in a transaction by default. Commands such as KILL cannot be run inside a transaction, so fail if called this way.

To prevent pymssql, or other python database connections, from adding this transaction, autocommit can be set to True.

As it is a stored procedure being called, callproc() may be more appropriate than execute().

import pymssql

conn = pymssql.connect(server, username, password, dbname)

conn.autocommit(True)

cursor = conn.cursor()

cursor.callproc("killconn")

Upvotes: 1

Related Questions