title: SQL. Can not rollback transaction
body: <p>I try to use transactions to delete few lines from my table. I use three times command delete, two of them are OK and third contains mistakes. I want to create save point and commit dropping of 2 objects. But I got an error: The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction. Here is my code:</p>
  
  <pre><code>BEGIN TRANSACTION
  BEGIN TRY
  DELETE FROM employee
  WHERE ID = 101; --OK
  
  DELETE FROM employee
  WHERE Name = 'James'; --OK
  
  SAVE TRANSACTION point_1;
  
  DELETE FROM employee
  WHERE ID = '11a'; --letters are not allowed
  COMMIT TRANSACTION
  END TRY
  BEGIN CATCH
      ROLLBACK TRANSACTION point_1
      PRINT 'Error! Not all employees were deleted from database.'
  END CATCH
  </code></pre>
  
  <p>Can anyone help me? Thank you very much!</p>
  
  <p>I tried this way:</p>
  
  <pre><code>    BEGIN TRANSACTION
  DELETE FROM employee
  WHERE ID = 101;
  
  DELETE FROM employee
  WHERE Name = 'James';
  
  SAVE TRANSACTION point_1;
  
  BEGIN TRY
  DELETE FROM employee
  WHERE ID = '11a';
  END TRY
  BEGIN CATCH
      if XACT_STATE () = -1
          ROLLBACK TRANSACTION
      if XACT_STATE () = 1 and @@TRANCOUNT = 0
          ROLLBACK TRANSACTION
      if XACT_STATE () = 1 and @@TRANCOUNT &gt; 0
          ROLLBACK TRANSACTION point_1
  PRINT 'Error! Not all employees were deleted from database.'
  END CATCH
  </code></pre>
  
  <p>There are no errors this time, but first two objects are not deleted. Where is my mistake? Thank you!</p>