Nested distributed transaction

Hello everybody-

I am trying to understand how to make distributed transactions in MS SQL 2005.

For example, I got two databases A and B and a client, connected to one of them - A. From that client I want to initiate transaction to B, using connection to A.

This functionality is available in Oracle using database links. With all new changes, does it exist in MS SQL 2005?

Thanks,

Alex.

For stating distributed transactions you could (for example):

1. Use statement BEGIN DISTRIBUTED TRANSACTION T-SQL statement

2. If you use .NET you could use TransactionScope class

|||

This is good. Then, in the BEGIN DISTRIBUTED TRAN... I need to explicitely say the Remote Server name to identify the target table. Do I register it with DTS or just as on the client alias?

Sorry for that questions - I am the Oracle specialist and trying to apply the same framework...

I am also interested if the distributed transactions work properly in the replicated environment (peer-to-peer). I mean, if I want to update a record on a remote site, but don't want this transaction to be replicated back to me - is that possible?

Thanks,

Alex.

|||

For call OtherServer.OtherDB.OtherSchema.OtherTable you need to add linked server for you SQL Server.

You could to do it at SQL Server Management Studio or by stored procedure sp_addlinkedserver

|||

Okay. But what about this part, this is quite important for my application:

I am also interested if the distributed transactions work properly in the replicated environment (peer-to-peer). I mean, if I want to update a record on a remote site, but don't want this transaction to be replicated back to me - is that possible?

Thanks again,

Alex.

|||It depended from type of your replication. If you use transactional replication, you could try to configure replication filters for ignoring, but it isn't simple task. In common case, for replication distributed and local changes don't have any difference.|||

I suppose I am planning to use peer-to-peer replication - where an object/record can be updated at any site. There I have a situation, where I want to update local record, which will be replicated to all other sites and - in the same transaction - the same record at a specific remote site - and this one I do not want to be replicated.

As I said, I can easily do it in Oracle for Multi-Master replication, but don't see it in the MSSQL2005.

Alex.