Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Transactions Expand / Collapse
Author
Message
Posted Tuesday, March 5, 2013 9:56 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 6:04 AM
Points: 269, Visits: 1,731
Is it possible to update 2 Db with 2 different sqlConnections under same Transaction ?

Thanks.
Post #1427163
Posted Tuesday, March 5, 2013 11:57 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:27 PM
Points: 7,107, Visits: 12,657
Not under the same transaction, but you can manage that on your own.

Connection 1: BEGIN TRAN
Connection 2: BEGIN TRAN
Connection 1: Modify some data
Connection 2: Modify some data

-- If modifications on connection 1 and 2 both succeed without error --
Connection 2: COMMIT TRAN
Connection 1: COMMIT TRAN

-- If modifications on connection 1 or 2 both encounter an error --
Connection 2: ROLLBACK TRAN
Connection 1: ROLLBACK TRAN

You could also look into leveraging the DTC (Distributed Transaction Coordinator). It is much more robust and does similar steps to what I have shown for you automatically. It can also work across different physical servers as well, and not only for database operations.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1427188
Posted Wednesday, March 6, 2013 12:22 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
this can put more light here
CREATE LOGIN login1 WITH PASSWORD = 'J345#$)thb';
CREATE LOGIN login2 WITH PASSWORD = 'Uor80$23b';
GO
CREATE USER user1 FOR LOGIN login1;
CREATE USER user2 FOR LOGIN login2;
GO
begin tran t
GRANT IMPERSONATE ON USER:: user2 TO user1;
SELECT SUSER_NAME(), USER_NAME();
EXECUTE AS user = 'user2';
REVERT;

GRANT IMPERSONATE ON USER:: user1 TO user2;
SELECT SUSER_NAME(), USER_NAME();
EXECUTE AS user = 'user1';
revert;
rollback tran t



-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1427202
Posted Wednesday, March 6, 2013 2:49 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 6:04 AM
Points: 269, Visits: 1,731
if connection 2: COMMIT TRAN is done initially (in code)
and some err in connection 1
then connection 2: cannot not be rolled back right ?
Post #1427253
Posted Wednesday, March 6, 2013 7:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:27 PM
Points: 7,107, Visits: 12,657
winmansoft (3/6/2013)
if connection 2: COMMIT TRAN is done initially (in code)
and some err in connection 1
then connection 2: cannot not be rolled back right ?

Correct. That's why you do leave TRAN on Conn 1 open while you do work on Conn 2, and then issue the commits one after the other. If the work succeeded, the COMMIT is almost guaranteed to succeed.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1427422
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse