0

When I try that my application kills an SQL Server process, through a FireDAC connection to that SQL Server, executing a KILL command, I get an KILL command cannot be used inside user transactions exception

FireDACConnection.Execute('KILL 65');

Ensuring that there is no transaction active before executing the command doesn't prevent FireDAC to start a new transaction, getting that error.

if FireDACConnection.InTransaction then FireDACConnection.Rollback;
FireDACConnection.Execute('KILL 65');

I have also tried to rollback the transaction inside the command, but then I get this exception Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements.

FireDACConnection.Execute('ROLLBACK; KILL 65');

How can I execute a command through FireDAC without automatically being wrapped within an user transaction ?. Thank you.

Marc Guillot
  • 6,090
  • 1
  • 15
  • 42
  • Hi Mark, not sure if this is relevant, but the Remarks section, 2nd para, [MS documentation](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/kill-transact-sql?view=sql-server-ver15) says "You can't kill your own process." – MartynA Sep 27 '21 at 09:18
  • ..you could suppress the transaction count mismatch exception by starting a transaction after kill `rollback;kill;begin transaction;` – lptr Sep 27 '21 at 09:49
  • Try `FDConnection1.TxOptions.AutoStart := false;`. – Brian Sep 27 '21 at 12:38
  • Hi @MartynA You are right, but it's not intended for killing its own process but for the Administrator to kill processes keeping deadlocks. – Marc Guillot Sep 27 '21 at 13:44
  • Thanks @Lptr It still throws an exception (Una transacción iniciada la final de un lote MARS todavía está activa al final del lote. La transacción se ha revertido). Something about a transaction started on a MARS batch is still active, and that the changes will be reverted. But seeing that the kill command doesn't run inside that transaction, the process remains killed. So it's still not clean, I will try to prevent that exception, but now at least I have a backup option that works. Thank you. – Marc Guillot Sep 27 '21 at 14:01
  • Thanks @Brian. It doesn't make a difference, I still get the same error. But lptr has made me realize that my second approach, although it triggers an exception, it also kills the process. – Marc Guillot Sep 27 '21 at 14:02

1 Answers1

2

FireDAC is a component that encapsulate a transaction. You must use another component to execute a batch with or without explicit transaction.

KILL is a transact SQL command that cannot be executed inside a transaction, because it forces a ROLLBACK to another session.

But I am very curious to know why do you need to use such a disastrous command. Generally this is not to be used in an application code nor in a code logic....

SQLpro
  • 3,994
  • 1
  • 6
  • 14
  • Mostly it would be an administrative option so I can kill processes when I detect that they keep a deadlock. I can just do the same from the SQL Server Management Studio, but directly on the application would be more convenient. – Marc Guillot Sep 27 '21 at 13:46
  • @MarcGuillot if there is a deadlock, SQL Server will automatically KILL one of the process. The internal deadlock survey process runs every 3 seconds and kill the transaction that has the less job to do for rollback, except if you have especially parameterized sessions with a SET DEADLOCK_PRIORITY... https://docs.microsoft.com/fr-fr/sql/t-sql/statements/set-deadlock-priority-transact-sql?f1url=%3FappId%3DDev15IDEF1%26l%3DFR-FR%26k%3Dk(SET_DEADLOCK_PRIORITY_TSQL);k(sql13.swb.tsqlresults.f1);k(sql13.swb.tsqlquery.f1);k(MiscellaneousFilesProject);k(DevLang-TSQL)%26rd%3Dtrue&view=sql-server-ver15 – SQLpro Sep 27 '21 at 15:40