SDAC connection error - "manual or distributed mode"?

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
TrevorB
Posts: 12
Joined: Wed 01 Oct 2008 08:33

SDAC connection error - "manual or distributed mode"?

Post by TrevorB » Wed 01 Oct 2008 08:47

Hi,

I've just updated from an older version of SDAC to 4.5.

We are now continually getting 'Cannot create new connection because in manual or distributed transaction mode' errors in code like that below:

Code: Select all

begin
  if CustomerNotesGrid.RowCount>1 then begin
    with DataResource do begin
      MSConnection.StartTransaction;
      try
        with MSDeleteCustomerNotes do begin
            ParamByName('URNToDelete').AsString:=SendingFrom.URNAssignedToOrder.Caption;
          Execute;
        end;
        with MSCustomerNotes do begin
          Active:=true;
          for RowLoop:=3 to CustomerNotesGrid.RowCount-1 do begin
            Append;
            FieldByName('CustURN').AsString:=SendingFrom.URNAssignedToOrder.Caption;

 ......
        end{for}
        MSConnection.Commit;
        ShowMessage('Customer notes saved successfully');
      except
        on E:Exception do begin
          ShowMessage('Customer notes could not be saved ('+E.Message+'), please retry.');
          MSConnection.Rollback;
          raise;
        end
      end;
      MSConnection.ApplyUpdates;
    end;
  end{if};
  Close
end;
The error occurs on the first 'execute'. I have read in other posts that the problem can occur if the the tables/queries fetchall is false. But none of our tables/queries use false, we have fetchall=true on all occasions.

The odd thing is this never happened in the previous version 4.35 and has only started since I updated to 4.5. Is it easy to return to version 4.35 if there is no solution to this problem because we really need the transactions to work?

Many thanks,

Trevor

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Thu 02 Oct 2008 09:24

I could not reproduce the problem.
Please send me a complete sample at dmitryg*devart*com to demonstrate it, including script to create and fill table.

TrevorB
Posts: 12
Joined: Wed 01 Oct 2008 08:33

Post by TrevorB » Thu 02 Oct 2008 12:01

Hi Dimon,

Thanks for the prompt reply.

This, I think, is where we will have problems :-(

When I create a small sample file, I cannot get it to reproduce this error. The error is happening in a project of 70,000+ lines and I obviously cannot send this project to you as a whole.

Can you think of any other way I can simulate what I am doing in the main program or suggest any settings I can check that might be causing this within the main program but not in a small sample.

I guess this is as frustrating for you as me that I cannot reproduce this on a small program and it only occurs on our main code.

I can't think of a way forward other than maybe to grant you some kind of Remote Desktop or PCAnywhere or VNC access to my PC to view the code and see the problem and allow you to check what is happening on the actual software and settings that I am using.

Trevor

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Tue 07 Oct 2008 09:44

It is possible that you are establish a new connection with a database table, in which changes are executed. It may occur on processing of AfterExecute or some other event.
Also check that the MSDeleteCustomerNotes.Connection property is set to MSConnection.
Maybe the DisconnectedMode or the LocalFailover properties are enabled in MSConnection.

TrevorB
Posts: 12
Joined: Wed 01 Oct 2008 08:33

Post by TrevorB » Fri 10 Oct 2008 08:46

Hi Dimon,

I have checked and rechecked your first suggestion to see if a new connection is established with a database and I don't think this is the case. For example, this can occur simply loading the program and going straight to an area that is editable without any other database activity.

The connection is correct in all instances.

And both of those properties are disabled.

Can you suggest anything else?

Thanks!

Trevor.

Challenger
Devart Team
Posts: 925
Joined: Thu 17 Nov 2005 10:53

Post by Challenger » Tue 14 Oct 2008 08:07

Please try to close all active datasets associated with this connection using, for example, the DataSets property of TMSConnection.

TrevorB
Posts: 12
Joined: Wed 01 Oct 2008 08:33

Post by TrevorB » Tue 14 Oct 2008 12:08

Okay.... if I put in a check beforehand that checks for open datasets, there are 10 or so. If I close these active connections in the 'check' loop before starting the transaction then the transaction is okay and no error is produced on the first 'active:=true'.

But, the problem with this is I need those other tables open to accrue the data I need as part of the main 'save'. Obviously, if I close these tables (and they are tables rather than stored procedures/queries) I get an error because the table is closed. I can't open these other tables after the 'starttransaction' because these tables are preloaded when the program is started - they are needed throughout the process and would be a significant overhead should we keep closing and re-opening the tables.

What has changed in this respect since 4.35 because in theat version of SDAC we didn't have this problem? It was only when we were just about to go 'live' with the system of which SDAC is part that we thoght it would be wise to update SDAC to the latest version. From that point the transaction encompassing code no longer worked but produced the error we are discussing.

So we have had to comment out the transaction parts of the code which isn't ideal as we have areas where a save can mean updating 4-5 tables and transactions are ideal for rolling back all the changes should any single one fail. For example, when we save an order, we have to update the order header, order lines, payments, order notes and customer notes table. With transactions, should any fail, we can rollback the whole lot, which is great. Without transactions (as we have it now), although it works, it is not ideal as the last update could fail for some reason and then everything is out of sync.

Thanks for your continued help, appreciated.

Trevor.

Challenger
Devart Team
Posts: 925
Joined: Thu 17 Nov 2005 10:53

Post by Challenger » Tue 14 Oct 2008 12:29

I don't want you to change the logic of your application. I just want to find the reason of the problem. I could not reporduce the problem, so I am asking you to perform some tests. Accroding to Microsoft documentation, the problem is in underfetched recordset. Please try to find the problem dataset.

TrevorB
Posts: 12
Joined: Wed 01 Oct 2008 08:33

Post by TrevorB » Tue 14 Oct 2008 13:46

Hi Challenger,

I think I owe you an apology and an awful lot of thanks......

Following your persuading me to persist with the investigation into unfectched rows, I was still convinced this wasn't the problem as I couldn't find any data type in the data module that had 'fetch all' properties set to false.

But following your convincing me to persist with this, I tracked each active data type connecting to MSConnection and found that one (hardly used of course) table had a FetchAll and FetchRows set in code (but not at design). I think this was done early on in the development to allow it to be used from home (i.e. fetching 30 rows rather than many 1000's). At the time this was switched so it could be turned on and off with a command parameter, but that code switch was removed but the properties were still set in code. Ooops.

So my apologies (huge ones!) and much, much thanks for persisting with me along those lines to get to the bottom of the problem that was ultimately mine.

Many thanks once again for your time and patience in helping me with this and I am relieved we can revert to transaction processing once again.

Thank you,

Trevor

Challenger
Devart Team
Posts: 925
Joined: Thu 17 Nov 2005 10:53

Post by Challenger » Wed 15 Oct 2008 05:34

Feel free to contact us with any other questions.

Post Reply