SQL Server - transactions roll back on error?

sql sql-server sql-server-2005 transactions
jonathanpeppers picture jonathanpeppers · Nov 17, 2009 · Viewed 209.5k times · Source

We have client app that is running some SQL on a SQL Server 2005 such as the following:

BEGIN TRAN;
INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
COMMIT TRAN;

It is sent by one long string command.

If one of the inserts fail, or any part of the command fails, does SQL Server roll back the transaction? If it does not rollback, do I have to send a second command to roll it back?

I can give specifics about the api and language I'm using, but I would think SQL Server should respond the same for any language.

Answer

user121301 picture user121301 · Nov 17, 2009

You can put set xact_abort on before your transaction to make sure sql rolls back automatically in case of error.

Related questions

Truncate Table Within Transaction

Can the SQL "truncate table" command be used within a transaction? I am creating an app and my table has a ton of records. I want to delete all the records, but if the app fails I was to rollback …

Read More
Add a column with a default value to an existing table in SQL Server

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?

Read More
Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More