SQL Server SET XACT_ABORT ON and JAVA

Question

I have a stored procedure that is working as expected. Snippet shown below:

BEGIN
SET XACT_ABORT ON;
BEGIN TRANSACTION;  

INSERT Table1...;
INSERT Table2...;

COMMIT TRANSACTION; 
END

If an error occurs in Table2, Table1's insert rolls back. The problem is with Java call to stored procedure. The code below will not throw exception if XACT_ABORT is on;

    psmt = conn.prepareStatement(query);
    psmt.setInt(1, id);
    psmt.execute();
    psmt.close();

I see when I exec stored procedure within SQL Server Management Studio, that it appears two messages are returned. One of success, one of error: though the inserts are rolled back.

(1 row(s) affected)
Msg 515, Level 16, State 2...

What is needed, within Java call, to get the exception/error recognized? ...To read all messages?

Answer 1

Try to add:

set nocount on;

at the top of the procedure?

Answer 2

I've struggled with XACT_ABORT in the not-so-distant past and your question took me on a trip down memory lane. My issues were similar to yours and it seems as if my fix will work just fine for your situation as well.
XACT_ABORT will roll back all transactions leaving you in the state in which you were in prior to your executing your code. That means that all records of the troublesome query, including the error message will be gone with the wind; there is, however, one place which is "safe" from this rollback, and that is a variable. You can leverage that to work for you, as I'll show you below.

Here is the code structure which I used:

BEGIN
    SET XACT_ABORT ON;
    BEGIN TRANSACTION;  
    DECLARE @MSG VARCHAR(MAX);

    BEGIN TRY
        INSERT Table1...;
    END TRY
    BEGIN CATCH
        SET @MSG = 'Table1: ' + ERROR_MESSAGE();
        GOTO failure; /* SEGMENT BELOW WHICH WOULD TAKE CARE OF YOUR ERROR MESSAGES*/
    END CATCH
    /* REPEAT ABOVE SEGMENT FOR ALL INSERTS*/

    GOTO SUCCESS; /* THIS IS HERE SO THAT YOU CAN SKIP THE FAILURE SEGMENT IN THE EVENT THAT ALL WENT WELL UP UNTIL HERE*/

    FAILURE:
        ROLLBACK; 
        RETURN @MSG;

    SUCCESS:
        COMMIT TRANSACTION; 
END