I have a simple unit test which is failing the final assertion:
public void ExecuteNonQueryWithCommand_ShouldUseTransaction()
const string insertString = "insert into Region values (77, 'Elbonia')";
const string countString = "select count(*) from Region";
const string deleteString = "delete from Region where RegionId = 77";
TdConnection cn = new TdConnection(db.ConnectionString);
TdCommand insertCmd = new TdCommand(insertString, cn);
TdCommand countCmd = new TdCommand(countString, cn);
TdCommand deleteCmd = new TdCommand(deleteString, cn);
int initialRows = (int)countCmd.ExecuteScalar();
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew))
int rows = insertCmd.ExecuteNonQuery();
int postScopeRows = (int)countCmd.ExecuteScalar();
I was under the impression per this MSDN documentation linked below (see the remarks section) that the transaction should roll back due to the lack of a scope.Complete() statement within the using block. Does Teradata support this feature?
The Teradata Database does not support 2PC or Distributed Transactions.
The Data Provider supports local transaction (TdConnection.BeginTransaction, TdTransaction ...). TdTransaction class supports IDisposable and it can be used in a Using-Clause. TdTransaction.Dispose method will automatically rollback an uncommitted transaction.
Refer to the Teradata Database Messages manual. It states:
3932 Only an ET or null statement is legal after a DDL Statement.
Explanation: A DDL statement was issued previously. By our current rules,
the only time that a DDL statement can be issued inside an explicit transaction is when
it is the last statement in that Transaction. In an explicit transaction, the only statements
allowed after a DDL statement are an END TRANSACTION , an unconditional ABORT, or a NULL statement.
Does the Multi-Statement SQL contain a DDL statement? if so, I suggest separating the DML statements from the DDL statement(s).
Yes, that was the problem - I was attempting to issue multiple DDL statements simultaneously. Thanks for the clarification.