Transaction with multiple Databases

[Guest]

Hello, I've got a problem. I need make a transaction with two distincts databases. The two database are SQL-SERVER. Please suggest me the best way to do it.

Best regards

 

[Cowboy \(Gregory A. Beamer\) [MVP]]

You have three options with transactions:

1. In SQL Server
2. In your code through ADO.NET
3. Via COM+ (Component Services)

With ADO.NET, you can set a transaction that spans multiple databases. You
end up setting up all of the code, which gives you great control, but also
great responsibility. This gets easier with .NET 2.0 next year.

With SQL Server, you will have to link servers to run the transaction. I
have never tried this personally, so I cannot vouch for it, but it makes a
lot of sense.

As a middleware solution, you can create a COM+ component using Enterprise
Services. You can then set up the component to automatically roll back the
entire transaction when there is an error. You also have manual control.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************
Think Outside the Box!
************************************************

Hello, I've got a problem. I need make a transaction with two distincts

databases. The two database are SQL-SERVER. Please suggest me the best way
to do it.

 

[Pablo Castro [MS]]

You need to use distributed transactions if you want to coordinate work in
two or more servers. "SqlTransaction" objects only work within a single
connection, to a single server.

Here are a couple of links with samples and details on how to do distributed
transactions in .NET:

http://support.microsoft.com/default.aspx?scid=kb;en-us;316247

http://msdn.microsoft.com/library/en-us/dndotnet/html/disttranvbnet.asp?frame=true&_r=1

If you're contacting the second server from T-SQL code in the first server,
and not directly from the client, you also have the option to use SQL Server
support for linked servers (check out "BEGIN DISTRIBUTED TRANSACTION" in SQL
Server Books Online)

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.

Hello, I've got a problem. I need make a transaction with two distincts

databases. The two database are SQL-SERVER. Please suggest me the best way
to do it.