Devart Forums

Posted: **Mon 22 Aug 2011 18:44**

I seem to be caught in a catch 22. I need to be able to open a large result set (~900K rows of ~5K bytes each), read each row, perform some processing and update each row. I am doing this with SQL Server 2008, dbExpress, Delphi 2007 and the DevArt driver.

If I open the result set with FetchAll=True, the driver tries to fetch all rows in the dataset quickly exhausting available memory. If I open the result set with FetchAll=False, the result set becomes non-updatable. I get the dreaded "Cannot create new connection because in manual or distributed transaction mode" error when I try to ApplyUpdates(). I have read the other posts on this forum regarding this error but am no nearer to a solution than before.

How can I write a Delphi program, using dbExpress, to read every row in a large table and update every row in that table? Without resorting to stored procedures or other server side jiggery pokery. Surely, this is a common thing to want to be able to do?

Posted: **Fri 26 Aug 2011 13:25**

Hello,

We are investigating this problem. As soon as we have any results we will let you know.

Posted: **Fri 02 Sep 2011 12:52**

We have investigated the problem. This problem is connected with the specificity of SQL Server work. To solve the problem, you should start transaction manually before opening a dataset. Also you should use Native Client provider and enable MARS. Here is an example:

Code: Select all

  SQLConnection1.VendorLib := 'sqlncli.dll';
  SQLConnection1.Params.Values['Custom String'] := 'MultipleActiveResultSets=True';
  SQLConnection1.Open;
  TD.TransactionID := 1;
  TD.IsolationLevel := xilREADCOMMITTED;
  SQLConnection1.StartTransaction(TD);
  ClientDataSet1.Open;

  // edit data

  ClientDataSet1.ApplyUpdates(0);
  TD.TransactionID := 1;
  TD.IsolationLevel := xilREADCOMMITTED;
  SQLConnection1.Commit(TD);

Posted: **Fri 02 Sep 2011 13:19**

Thank you

Devart Forums

Cannot create new connection because in manual or ... error

Cannot create new connection because in manual or ... error