Problem with using transaction in delphi when calling to MS SQL SERVER 2008 R2?

I need to call some Stored Procedures from Delphi and because they are related I have to use transactions.

But It always returns an error when called :

'Transaction cannot have multiple recordsets with this cursor type. Change the cursor type ,commit the transaction, or close one of the recordsets.'

And this error only occurs for MS SQL SERVER 2008, when I use MS Access It works fine.

Whats the problem ?

Thanks in advance

UPDATE :

procedure TF开发者_运维百科orm1.Button2Click(Sender: TObject);  
begin  
    if not DM.ADOConnection.InTransaction then  
        dm.ADOConnection.BeginTrans;  
    ADOQuery.LockType := ltBatchOptimistic;  
    ADOQuery.CursorType := ctUnspecified;  
    Try  
        with ADOQuery do  
        begin  
            Close;  
            SQL.Clear;  
            SQL.Text := 'INSERT INTO [UserAction] (UAct_Frm_ID,UAct_Type,UAct_Description'  
              +',UAct_Date,UAct_Time,UAct_Usr_ID)'
              +'VALUES(:UAct_Frm_ID'
              +',:UAct_Type,:UAct_Description,:UAct_Date,:UAct_Time'
              +',:UAct_Usr_ID)';
  Parameters.ParamByName('UAct_Frm_ID').Value := 1;
  Parameters.ParamByName('UAct_Type').Value := 1;
  Parameters.ParamByName('UAct_Description').Value := 'test by Q1';
  Parameters.ParamByName('UAct_Date').Value := completdate(datenow);
  Parameters.ParamByName('UAct_Time').Value := TimeToStr(Now);
  Parameters.ParamByName('UAct_Usr_ID').Value := 1;
  ExecSQL;
  end;
  Except
    DM.ADOConnection.RollbackTrans;
    ShowMessage('RollBack');
    Exit;
  End;
  dm.ADOConnection.CommitTrans;
  ShowMessage('Commite');
end; 

From here:

Resolution:

Use a different cursor type, change the cursor location to adUseClient or close the first recordset before opening another on the same connection/transaction.

Cause:

SQL Server can only open one ForwardOnly cursor at a time on a connection, because SQL Server can only process one active statement at a time per connection.

When you try to open more than one ForwardOnly ADO recordset at a time on a single Connection, only the first ADO recordset is actually opened on the Connection object. New, separate connections are created for subsequent ForwardOnly cursors.

A transaction is on a single connection. When you attempt to open more than one ForwardOnly recordset within a single transaction, ADO attempts to open more than one ForwardOnly recordset on the connection of the transaction. An error occurs because SQL Server only allows one ForwardOnly recordset on a single connection. Because the error is within a manual transaction, you might see the error above. Microsoft Data Access Objects 2.1 Service Pack 2 and later versions of MDAC contain more informative error messages. For that reason, you may see the more informative second or third error message, above.

Try with including [eoExecuteNoRecords] into ExecuteOptions.