Executing sql linked server procedures through python

Question

I am getting an error when trying to execute SQL Server system procedures such as sp_addLinkedServer and sp_dropserver etc from python script

      USE [master]

      EXEC master.dbo.sp_dropserver @server=N'DS', @droplogins='bs'

Error :

          (15002, b"The procedure 'sys.sp_dropserver' cannot be executed within a transaction.DB-Lib 
          error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL 
          Server\n")

Answer 1

The error is quite clear - you can't run this SP in a transaction, Some libraries (ODBC etc) support implicit transactions (meaning every piece of SQL will be run in a transaction) so you need to dig into why these are enabled. Explained in the link below

or just add SET IMPLICIT_TRANSACTIONS OFF in the line before the SP call

https://stackoverflow.com/questions/60511929/set-set-implicit-transactions-attribute-on-off-for-a-pyodbc-connection