Rollback whole sql insert if anything fails

Question

I'm trying to run multiple inserts on different tables and I want them all to rollback if anything fails.  Now I have the sql code working and it's rolling back correctly, but it is not raising the error over odbc.  JMP just returns a missing value. Anyone know how to make sure the raiserror() shows up correctly over ODBC?  It raises the error correctly if I run the same code directly in SQL.

dbc = Create database connection("DRIVER=SQL SERVER;SERVER=.\;Database=Test");
rfail = executeSQL(dbc, 
	"
	DECLARE @TransactionName varchar(20) = 'Transaction1';
	BEGIN TRY
		BEGIN TRAN @TransactionName

INSERT INTO [dbo].Test1
						(A --varchar
						,B --Int
						)
					VALUES
						('Test Fail'
						,1
						)
			DECLARE @ID bigint
			SELECT @ID = SCOPE_IDENTITY()

INSERT INTO [dbo].Test2
						(T1ID --Test1 ID
						,A --varchar
						)
					VALUES
						(@ID
						,14
						)
		COMMIT TRAN @TransactionName
	END TRY
	BEGIN CATCH
		DECLARE @ErrorMessage NVARCHAR(4000);
		DECLARE @ErrorSeverity INT;
		DECLARE @ErrorState INT;
		IF @@TRANCOUNT > 0
			ROLLBACK TRAN @TransactionName
		SELECT
			@ErrorMessage=ERROR_MESSAGE(),
			@ErrorSeverity=ERROR_SEVERITY(),
			@ErrorState=ERROR_STATE();
		RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)
	END CATCH
	",

);

close database connection(dbc);

pmroz · Answer

This is a crude solution, but before you do your transaction insert a value into a status table like "Processing", and commit that.

Then do your transaction.  After you do the commit change the status table value to "Done".  If your transactions fail and you jump to the rollback section your status table will still show "Processing".  So after the call for your main transactions check your status table.  If it still says "Processing" you'll know that a rollback was performed.

vince_faller · Answer

No love.  Still get nothing back.

gzmorgan0 · Answer

@vince_faller, I still consider myself an SQL "hacker" so by no means, an expert. Just looking at your SQL and knowing that a BEGIN TRAN ends with a COMMIT TRAN or a ROLLBACK TRAN, maybe the SELECT section of SQL for capturing the error messages should occur before the ROLLBACK ???

This is just a wild guess. Please post if you find a solution.