CODEWITHSUNDEEP
sql-serverodbccommit
david a.
david a.

Reputation: 5291

SQL Server: How to make a transaction commit fail?

To test error handling in an application, I'm looking for a way to let a transaction commit result in an error.

The application is written in C and uses ODBC to talk to a SQL Server 2017 data source. The application starts a database transaction and executes an arbitrary SQL (which I can change for the sake of the test). Then, it commits the transaction (using ODBCs SQLEndTran()). I want to build a test that verifies the error handling of the commit.

Is there an easy way and reliable way to let the commit fail, e.g by executing some specific SQL script before the commit, or by changing the database or the data source settings?

EDIT / clarification: What I need to fail is the transaction commit itself (specifically the SQLEndTran() complete call with an error). SQL before that shall complete successfully.

Upvotes: 2

Views: 1738

Answers (2)

Piotr Palka
Piotr Palka

Reputation: 3159

If you are able to time it correctly in a testing framework you can do few things:
1. Kill session from a separate connection in a testing framework.
2. Change firewall configuration to emulate network error.
3. Switch database to single user mode or stop SQL Service.

Upvotes: 3

Sean Lange
Sean Lange

Reputation: 33571

Easiest way is to force a divide by zero.

declare @SomeVal int = 0
set @SomeVal = 2 / @SomeVal

--EDIT--

Since I guess you want the commit to fail you could simply add a rollback right before the commit. Then the exception would be thrown on the commit statement.

Upvotes: 2

Related Questions