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);
@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.
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.