Linked Server & MS DTC

Linked Server & MS DTC

Post by Tom Antol » Wed, 28 Mar 2001 08:08:37



Hi,

Let's say I want to write a classic bank transaction, move cash from my
savings account into my checking account.  Typically one would wrap this
inside a transaction.

Here's the twist I am curious in.  Say my savings account lives in my
savings database on Server A and my checking account lives in my checking
database on Server B.  Server B is a linked server on Server A.

If I want to write a transaction in a stored procedure that is running on
Server A, does MS DTC have to be involved (running)?  Do I need to say
"BEGIN DISTRIBUTED TRANSACTION"

Any help is greatly appreciated.

TIA,
Tom

 
 
 

Linked Server & MS DTC

Post by Umachandar Jayachandra » Wed, 28 Mar 2001 08:26:30


Quote:>> "BEGIN DISTRIBUTED TRANSACTION"

    Yes, this is one way. But you can just do "BEGIN TRAN" & have the "SET
REMOTE_PROC_TRANSACTIONS" setting ON at connection level or server-level.
This way you don't have to code the SPs differently & by using views against
the tables, you could move the tables to any server/database as you wish
without affecting any code.

Quote:>> Server A, does MS DTC have to be involved (running)?

    Yes, if you are doing server-side transactions. There is another way to
start the entire transaction on the client-side & use a different
coordinator. This will require more programming though.

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )

 
 
 

Linked Server & MS DTC

Post by Nesaa » Wed, 28 Mar 2001 18:03:29


Hi,

Does the same apply to moving data between servers inside a trigger?
My insert trigger inserts the data correctly between server A and server B.
But doing an update or delete just causes the execution to hang and
eventually the services have to stopped and restarted on the source server

Thanks
Nesaar


Quote:> >> "BEGIN DISTRIBUTED TRANSACTION"

>     Yes, this is one way. But you can just do "BEGIN TRAN" & have the "SET
> REMOTE_PROC_TRANSACTIONS" setting ON at connection level or server-level.
> This way you don't have to code the SPs differently & by using views
against
> the tables, you could move the tables to any server/database as you wish
> without affecting any code.

> >> Server A, does MS DTC have to be involved (running)?

>     Yes, if you are doing server-side transactions. There is another way
to
> start the entire transaction on the client-side & use a different
> coordinator. This will require more programming though.

> --
> Umachandar Jayachandran
> SQL Resources at http://www.umachandar.com/resources.htm
> ( Please reply only to newsgroup. )

 
 
 

Linked Server & MS DTC

Post by Umachandar Jayachandra » Thu, 29 Mar 2001 05:41:31


Quote:>> Does the same apply to moving data between servers inside a trigger?

    Yes, if the server setting 'remote proc trans' is enabled. See
sp_configure output or SQLEM server properties.

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )

 
 
 

1. linked server, DTC, SQLOLEDB, Access, linked tables = HEADACHE!

OK, welcome to my nightmare.... I got 2 SQL Servers and an Access
DB Front end

SQL Server1: SQL Server 7.0 (on our network)
SQL Server2: SQL Server 2000 (remote)
AccessDB: on our network

There is a Form in the Access DB that is used by Sales & Marketing to
update data in both SQL Servers and there are subqueries involved. I
tried just linking the tables in Access, but the connection to the
remote SQL Server keeps getting dropped within seconds of opening it
regardless of whatever timeouts I set anywhere. The linked tables to the
local SQL Server stay connected just fine (both DSNs on the Access
machine are using TCP/IP).

So, I tried adding SQL Server2 as a Linked Server to SQL Server1, then
write a distributed view object (SELECT * FROM
[linkedserver].[catalog].[dbo].[table]) the table I need. Then I create
a linked table in Access to the new view. Well I can SEE the data fine
(SELECT), but in order to update the data, it evidently requires DTC,
but since it's SQLOLEDB (all together now) "does not support distributed
transactions." Yup, DTC is running just fine on both SQL Servers; nope,
we can't upgrade the SQL Server 7.0

If anyone has ANY insight into this mess, I'm all ears.

--
Posted via http://dbforums.com

2. Consultas asincronas con ado

3. SQL & network gurus: MS DTC problem

4. getting distinct values

5. Can I get a table name from a Recordset?

6. Linked server, no dtc, implicit transactions

7. "Short-circuit" a query?

8. DTC, VB 6.0, Triggers, Linked Servers

9. Linked Server examples - DTC/MTS

10. SQL Server 6.5 and MS DTC

11. Error 266 in MS DTC with SQL Server 6.5

12. Distributed SQL Server databases (MS DTC)