G
Gerry Schatte
I am attempting to put a large process inside a transaction using DAO and
linked tables to a SQL server back end. I am doing so because the procedure
has a history of failing and leaving the data in an inconsistent state
requiring a restore.
The process pulls several recordsets and modifies several tables, and does
not take much time to complete without a transaction. Once I implemented the
transaction, I have seen nothing but problems. The biggest of the problems
is that the entire process seems to just hang at certain spots. This
"hanging" occurs at different locations depending on which client database I
use, so it appears to be some limitation I'm reaching.
If I run a SQL trace while the procedure is processing, I can see that there
is indeed activity, but its as if there's a deadlock or something. This
usually results in OCBC call failed or failed to insert into an ODBC linked
table. If I remove the transaction, the process completes as expected.
Would I be better off just cutting my losses and converting the whole thing
to ADO? It seems like DAO / ODBC does not like working with transactions at
all.
Thanks,
Gerry
linked tables to a SQL server back end. I am doing so because the procedure
has a history of failing and leaving the data in an inconsistent state
requiring a restore.
The process pulls several recordsets and modifies several tables, and does
not take much time to complete without a transaction. Once I implemented the
transaction, I have seen nothing but problems. The biggest of the problems
is that the entire process seems to just hang at certain spots. This
"hanging" occurs at different locations depending on which client database I
use, so it appears to be some limitation I'm reaching.
If I run a SQL trace while the procedure is processing, I can see that there
is indeed activity, but its as if there's a deadlock or something. This
usually results in OCBC call failed or failed to insert into an ODBC linked
table. If I remove the transaction, the process completes as expected.
Would I be better off just cutting my losses and converting the whole thing
to ADO? It seems like DAO / ODBC does not like working with transactions at
all.
Thanks,
Gerry