Nested Distributed Transactions?

Hi All,

Is it possible to have a Distributed Transaction running within another distributed transaction and so on.

I have 3 servers(Servers A, B, and C). Server A has a linked server to Server B and Server B has a linked server to Server C. I have two triggers. One that resides on Server A and Updates Server B and Another on Server B that updates Server C. Trigger on Server A invokes a distributed transaction with Server B to Update it which in turn fires the trigger on Server B which invokes another distributed transaction with Server C.

Now is this possible?? If not how can I get Around this?? I can not just a distributed transaction on the trigger which resides on server A as the one on Server B can fire on its own and not always because of Server A.

Is it possible to have nested distributed transactions like Begin/Ends??

Thanks
AnthonyShould be OK.

Every trigger runs within a transaction and this should be automatically upgraded to a distributed transaction for cross server operations so you shouldn't have to do any coding aprt from the set xact_abort on.

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.

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.