Guys,
I am trying to use transactions to do some database archiving, which
involves the TransactionScope object (all in vb.net)
However, when I run it through it bombs out at various points with the
above error message, there seems no logical reason for it doing this.
I then added a timeout to the transaction, as follows, which seemed to
help to some degree, i.e. it was failing so early on, however it still
fails with the same error further down the line.
'
'Attempt to archive the data
'
Dim TransOptions As New TransactionOptions
Dim TransScope As New TransactionScopeOption()
TransOptions.Timeout = System.TimeSpan.FromMinutes(300)
'Start the connection and transaction to the archive DB
Using scope As New TransactionScope(TransScope,
TransOptions)
The full section of code is below:
Try
'
'Attempt to archive the data
'
Dim TransOptions As New TransactionOptions
Dim TransScope As New TransactionScopeOption()
TransOptions.Timeout = System.TimeSpan.FromMinutes(300)
'Start the connection and transaction to the archive DB
Using scope As New TransactionScope(TransScope,
TransOptions)
Using archiveConnection As OracleConnection =
OracleDatabaseHelper.SetupOracleConnection(AppConfig.ArchiveDB)
archiveConnection.Open()
'Try to archive the database tables
Logger.LogDebug("Saving the EX_PKGMSG_ASSOC table
data")
statusReport.ExPkgMsgAssocArchived =
saveListOfExPkgMsgAssoc(exPkgMsgAssoc, archiveConnection,
statusReport)
Logger.LogDebug("Saving the EX_PKGMSG_DET data")
statusReport.ExPkgMsgDetArchived =
saveListOfExPkgMsgDet(exPkgMsgDet, archiveConnection, statusReport)
Logger.LogDebug("Saving the EX_PKGMSG_HDR data")
statusReport.ExPkgMsgHdrArchived =
saveListOfExPkgMsgHdr(exPkgMsgHdr, archiveConnection, statusReport)
Logger.LogDebug("Saving the EX_MSG_CONTENT data")
statusReport.ExMsgContentArchived =
saveListOfExMsgContent(exMsgContent, archiveConnection, statusReport)
'Set the status of the archive part to successful,
this mite be overwritten later to failure if we
'don't successfully purge the table
statusReport.setDBArchiveSuccessful()
'
'Purge the data from the source database
'
Using sourceConnection As OracleConnection =
OracleDatabaseHelper.SetupOracleConnection(AppConfig.SourceDB)
sourceConnection.Open()
statusReport.ExPkgContentDeleted =
deleteListOfExMsgContent(exMsgContent, sourceConnection)
statusReport.ExPkgMsgAssocDeleted =
deleteListOfExPkgMsgAssoc(exPkgMsgAssoc, sourceConnection)
statusReport.ExPkgMsgDetDeleted =
deleteListOfExPkgMsgDet(exPkgMsgDet, sourceConnection)
statusReport.ExPkgMsgHdrDeleted =
deleteListOfExPkgMsgHdr(exPkgMsgHdr, sourceConnection)
'
'Commit the changes
'
End Using
End Using
'The complete method commits the transaction. If an
exception has been thrown
'complete is called and the transaction is rolled back
scope.Complete()
End Using