BorlandTalk.com

Jan Doggen · Guest

Hi

Problem: 'cannot create new transaction because capacity is exceeded'
but CloseDataSets before StartTransaction is not an option.

This is the code:
- QryLookup (simple SELECT) is opened.
- WHILE NOT QryLookup.EOF
Put SQL with data from QryLookup into QryUpdate
(CloseDataSets)
StartTransaction
try
QryUpdate.ExecSQL;
commit
except
rollback
error message
end;

If I put CloseDataSets before the StartTransaction QryLookup gets closed Wink

In *this* specific example I can bring CloseDataSets/StartTransAction
outside of the WHILE
loop (this changes the granularity of the transactions though).
But this is not always an option, because this looping/updating happens in a
lot of places,
e.g. with ClientDataSets showing data in a grid. I cannot always afford to
'close all datasets'.

Extra info:
- Delphi 7.1, DBExpress
- TSQLConnection to MSSQL Server 7, KeepConnection=TRUE, Multiple
Transaction=TRUE
- TSQLQueries have GetMetaData=FALSE
- MDAC 2.8
- Up to this point, no ClientDataSets were opened
- QryLookup and QryUpdate access the same database table

I'm getting pretty desperate here ;-(
Any suggestions as to what else I can try? Much appreciated!

Thanks
Jan

--
---------------------------------------------------------------------------
Jan Doggen, QSA Landsmeer, The Netherlands
Please remove the spam blocker from my email address when replying directly

Jan Doggen · Guest

I have now transferred all TQueries used for updating to an identical
TSQLConnection (name SCCConfigUpdate). All loookup queries go through the
'old' connection. Therefore no read-only unidirectional queries are
connected through SCCConfigUpdate and I can use transactions without the
CloseDataSet 'fix'.

Jan

"Jan Doggen" <j.doggen (AT) BLOCKqsa (DOT) nl> wrote