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

[sboydlns]

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?

[AndreyZ]

Hello,

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

[AndreyZ]

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);

[sboydlns]

Thank you