189598_en-US

liliconcom

Rollback whole sql insert if anything fails

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

Re: Rollback whole sql insert if anything fails

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

Re: Rollback whole sql insert if anything fails

No love.  Still get nothing back.  

Re: Rollback whole sql insert if anything fails

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.