G
Guest
I'm trying to use sqltransaction for updates across multiple databases but
can't quite figure out how to do it. Since the sqlconnection will be
connected to a single database, it would appear that I would need multiple
sqltransactions, which would keep from having true rollback and commit for
the updates.
A vb.net example of what I'm trying to do would be as follows. This is
simply trying to copy transactions from a live database to a historical
database then remove the transaction from the live database. I know there
are other ways to do this but it gives an example of my problem:
Dim sqlConnection1, sqlConnection2 As New SqlConnection()
sqlConnection1.ConnectionString = "Persist Security Info=False;
Integrated Security=SSPI;database=livedatabase;
server=mySQLServer;Connect Timeout=30"
sqlConnection2.ConnectionString = "Persist Security Info=False;
Integrated Security=SSPI;database=historydatabase;
server=mySQLServer;Connect Timeout=30"
Dim SqlTrans1, SqlTrans2 As SqlTransaction
'Copy to history and remove from current
SqlTrans1 = sqlConnection1.BeginTransaction
SqlTrans2 = sqlConnection2.BeginTransaction
Try
insert commands to history database go here (commands have .transaction
property set to SqlTrans2)
Try
delete commands from live database go here (commands have .transaction
property set to SqlTrans1)
Catch e As Exception
SqlTrans1.Rollback()
SqlTrans2.Rollback()
End Try
Catch e As Exception
SqlTrans2.Rollback()
End Try
'commit routine
Try
SqlTrans2.Commit()
Try
SqlTrans1.Commit()
Catch e As Exception
SqlTrans1.Rollback()
End Try
Catch e As Exception
SqlTrans1.Rollback()
SqllTrans2.Rollback()
End Try
The problem with the above fragment is that if the Commit to the
remove from the live database (SqlTrans1.Commit) fails, I've already
performed the commit to the write to the history database
(SqlTrans2.Commit) so I'm not able to roll the history database back
to the previous state.
Am I failing to understand something about the behavior of sqltransaction?
can't quite figure out how to do it. Since the sqlconnection will be
connected to a single database, it would appear that I would need multiple
sqltransactions, which would keep from having true rollback and commit for
the updates.
A vb.net example of what I'm trying to do would be as follows. This is
simply trying to copy transactions from a live database to a historical
database then remove the transaction from the live database. I know there
are other ways to do this but it gives an example of my problem:
Dim sqlConnection1, sqlConnection2 As New SqlConnection()
sqlConnection1.ConnectionString = "Persist Security Info=False;
Integrated Security=SSPI;database=livedatabase;
server=mySQLServer;Connect Timeout=30"
sqlConnection2.ConnectionString = "Persist Security Info=False;
Integrated Security=SSPI;database=historydatabase;
server=mySQLServer;Connect Timeout=30"
Dim SqlTrans1, SqlTrans2 As SqlTransaction
'Copy to history and remove from current
SqlTrans1 = sqlConnection1.BeginTransaction
SqlTrans2 = sqlConnection2.BeginTransaction
Try
insert commands to history database go here (commands have .transaction
property set to SqlTrans2)
Try
delete commands from live database go here (commands have .transaction
property set to SqlTrans1)
Catch e As Exception
SqlTrans1.Rollback()
SqlTrans2.Rollback()
End Try
Catch e As Exception
SqlTrans2.Rollback()
End Try
'commit routine
Try
SqlTrans2.Commit()
Try
SqlTrans1.Commit()
Catch e As Exception
SqlTrans1.Rollback()
End Try
Catch e As Exception
SqlTrans1.Rollback()
SqllTrans2.Rollback()
End Try
The problem with the above fragment is that if the Commit to the
remove from the live database (SqlTrans1.Commit) fails, I've already
performed the commit to the write to the history database
(SqlTrans2.Commit) so I'm not able to roll the history database back
to the previous state.
Am I failing to understand something about the behavior of sqltransaction?