Is rollback required if the request is completed with errors?

Question

I have a query like this:

use DataIncremental go DECLARE @row_terminator CHAR; SET @row_terminator = CHAR(10); -- or char(10) BEGIN TRAN tran2 DECLARE @stmt NVARCHAR(2000); SET @stmt = ' BULK INSERT accn_adjustments FROM ''C:\Users\agordon\Desktop\incrementaljan2012\accn_adjustments_201112302112.txt'' WITH ( firstrow=2, FIELDTERMINATOR = ''|'' , ROWS_PER_BATCH=10000 ,ROWTERMINATOR=''' +@row _terminator+''' )' exec sp_executesql @stmt; SET @stmt = ' BULK INSERT accn_adjustments FROM ''C:\Users\agordon\Desktop\incrementaljan2012\accn_adjustments_201112312112.txt'' WITH ( firstrow=2, FIELDTERMINATOR = ''|'' , ROWS_PER_BATCH=10000 ,ROWTERMINATOR=''' +@row _terminator+''' )' exec sp_executesql @stmt; SET @stmt = ' BULK INSERT accn_adjustments FROM ''C:\Users\agordon\Desktop\incrementaljan2012\accn_adjustments_201201022101.txt'' WITH ( firstrow=2, FIELDTERMINATOR = ''|'' , ROWS_PER_BATCH=10000 ,ROWTERMINATOR=''' +@row _terminator+''' )' exec sp_executesql @stmt; SET @stmt = ' BULK INSERT accn_adjustments FROM ''C:\Users\agordon\Desktop\incrementaljan2012\accn_adjustments_201201032101.txt'' WITH ( firstrow=2, FIELDTERMINATOR = ''|'' , ROWS_PER_BATCH=10000 ,ROWTERMINATOR=''' +@row _terminator+''' )' exec sp_executesql @stmt; 

The reason I put begin tran tran2 is to make sure that if there are errors, I can just do rollback

I ran the code and I got the message "query completed with errors"

SSMS did NOT indicate that some rows were inserted , as they usually do.

When I tried to do rollback tran tran2 , he said this transaction never started

So my question is: were there any rows bound to the database or not?

If not, why did he say that “the request completed with errors”, should he not simply say that the request did not complete due to errors?

Answer 1

Description

With SQL Server 2005, you can use error handling with TRY CATCH

TRY ... CATCH (Transact-SQL) Implements error handling for Transact-SQL, which is similar to exception handling in Microsoft Visual C # and Microsoft Visual C ++. A group of Transact-SQL statements can be enclosed in a TRY block. If an error occurs in the TRY block, control is transferred to another group of statements, which is enclosed in the CATCH block.

Example

 BEGIN TRY BEGIN TRANSACTION -- do something COMMIT TRAN -- Transaction successfull, commit! END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRAN --RollBack if error occured END CATCH 

Additional Information

• Microsoft TechNet - TRY ... CATCH (Transact-SQL)

Answer 2

From the BULK INSERT documentation :

The BULK INSERT statement can be executed within a user-defined transaction. Rollback of a user transaction using the BULK INSERT and BATCHSIZE statements to import data into a table or view using multiple batches returns all batches sent to SQL Server.

sp_executesql is contained in the outermost transaction (the one you start with BEGIN TRANSACTION ), so these bulk inserts must be transactional as a unit.

As follows from a comment by @MartinSmith, XACT_ABORT may have aborted this same external transaction if it started too long.