sqltransaction and updates to multiple databases

[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?

 

[Sahil Malik [MVP]]

Louis,

You have correctly identified that SqlTransaction will not bind two
databases in one transaction. You have the following choices --

a) Use a distributed transaction, read here for more info -
http://codebetter.com/blogs/sahil.malik/archive/2005/04/11/61755.aspx
b) Wrap this commit/rollback logic inside a stored procedure using Linked
Servers/Linked Tables.
c) Wrote your own rollback logic - which admittedly is a royal pain in the
butt.

Of course if you are using .NET 2.0, just use System.Tx and life is a lot
better.

HTH.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx

 

[Guest]

Thanks for the quick response. I sort of figured that was a limitation. I'm
not planning a move to .NET 2.0 until I guess SP3 comes out? Or is Microsoft
getting things right these days by SP2? ;-)

Since all of this is SQL Server 2000, I should be able to wrap up any
necessary updates within a stored procedure. According to Books Online, "if
a local transaction spans two or more databases on the same server, SQL
server uses an internal two-phase commit to commit all the databases involved
in the transaction."

Sounds like that will cover my requirements but requires me to move more of
the business logic down to T-SQL when I was trying to keep it in the VB.NET
code.