Re: sqltransaction and updates to multiple databases

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.



"Sahil Malik [MVP]" wrote:

> 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
> ***.
>
> 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
> -------------------------------------------------------------------------------------------
>
>
> "Louis McCutchen" <louismcc@xxxxxxxxxxxxxxxx> wrote in message
> news:1D5ECFFB-CFD9-4A6A-AA1A-56341493DD10@xxxxxxxxxxxxxxxx
> > 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?
> >
>
>
>
.