Distributed transaction

Question

Distributed transaction

miss.delinda

SSCommitted

Points: 1616
More actions
November 7, 2010 at 6:32 am

#388180

Hi,
I want to perform insert statement into db1 located at 192.168.1.34. At the same time, me also want to perform update statement into db2 located at 192.168.1.37.
What's the technologies should I use? As a result, this distributed transaction guaranteed success, or none of them is success.
I heard about Microsoft Distributed Transaction Coordinator (MS DTC). Looks like hard to implement. Any alternative?
Need advice

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

miss.delinda (11/7/2010)
I want to perform insert statement into db1 located at 192.168.1.34. At the same time, me also want to perform update statement into db2 located at 192.168.1.37.
What's the technologies should I use? As a result, this distributed transaction guaranteed success, or none of them is success.
I heard about Microsoft Distributed Transaction Coordinator (MS DTC). Looks like hard to implement. Any alternative?

No alternative to DTC. DTC implementation is pretty straight forward, once installed just use "begin distributed transaction" and do your stuff. Check here for details... http://msdn.microsoft.com/en-us/library/ms188386.aspx

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

miss.delinda SSCommitted Points: 1616 More actions · Answer 2

Hi Sir,

To using this statement, no need to setup Linked Server first right?

miss.delinda SSCommitted Points: 1616 More actions · Answer 3

Hi Sir,

Without Linked Server is setup, and

Based on article on http://msdn.microsoft.com/en-us/library/ms188386(v=SQL.90).aspx

I was run T/SQL as follow,

SET XACT_ABORT ON

BEGIN DISTRIBUTED TRANSACTION;

-- Insert

insert into tPurchaseOrder values('1926','Purchase Order');

-- Insert

insert into [MAFC-SVR1\NAV1].st3db.dbo.tDeliveryNote(no,remark) values('4475','1926~Purchare Order');

COMMIT TRANSACTION;

GO

The error was shown,

Could not find server 'MAFC-SVR1\NAV1' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

Really looking for help

tfifield SSCrazy Eights Points: 9655 More actions · Answer 4

Delinda,

It looks like the linked server has not been set up properly. It's not very hard to do once you get the hang of it.

In SSMS go to Server Objects, which is below all of the databases. Open it up and you'll see linked servers. Verify that you have one there properly set up.

Todd Fifield

miss.delinda SSCommitted Points: 1616 More actions · Answer 5

Sir,

Did we've a choice to setup another technical things instead of Linked Server?

I'm heard, Linked Server have a potential to interrupt a performance

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 6

miss.delinda (12/28/2010)
The error was shown,
Could not find server 'MAFC-SVR1\NAV1' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

Error message is pretty clear.

Do you have a linked-server called 'MAFC-SVR1\NAV1' ?

If not, add it using sp_addlinkedserver

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 7

While being specific with BEGIN DISTRIBUTED TRANSACTION is a good practice, I will say that the DB Engine is smart enough to know that it is a distributed transaction and promotes it to one without have to specify DISTRIBUTED..

CEWII

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 8

Elliott Whitlow (12/30/2010)
While being specific with BEGIN DISTRIBUTED TRANSACTION is a good practice, I will say that the DB Engine is smart enough to know that it is a distributed transaction and promotes it to one without have to specify DISTRIBUTED...

I do agree but, I would like to add that on top of being good practice to code exactly what you intend to do, coding BEGIN DISTRIBUTED TRANSACTION - when you are doing one - allows for remote storedproc execution without having to even think about other settings.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

miss.delinda SSCommitted Points: 1616 More actions · Answer 9

miss.delinda

SSCommitted

Points: 1616

January 1, 2011 at 11:59 am

#1268244

i want to say to thank you for all answer given