How can I use transactions that span procedures chained across multiple servers?

Question

I'm trying to test a proposition that one of our vendors presented to us for accessing their product database and it regards to queries and transactions that span multiple servers. I've never done this directly on the database before and to be frank, I'm clueless, so I'm trying to mock up a proof that this works at least conceptually.

I've got two SQL Server 2005 servers. Let's for argument's sake call them Server1 and Server2 [hold your applause] each containing a dummy database. The dummy database on Server1 is called Source and that on Server2 is called Destination, just to keep things simple. The databases each hold a single table called Input and Output respectively, so the structure is quasi explained like so:

• Server1.Source.dbo.Input
• Server2.Destination.dbo.Output

I have a stored procedure on Server2 called WriteDataToOutput that receives a single Varchar argument and writes it's content to the output table.

Now the trickiness starts:

1. I want to create a stored procedure on Server1.Source that calls the WriteDataToOutput stored procedure defined on Server2, which seems like the simple step.
2. I want this call to be part of a transaction so that if the procedure that invokes it fails, the entire transaction is is rolled back.

And here endeth my knowledge of what to do. Can anyone point me in the right direction? I tried this on two different databases on the same server, and it worked just fine, leading me to assume that it will work on different servers, the question is, how do I go about doing such a thing? Where do I start?

Answer 1

As others have noted, I agree that a linked server is the best way to go.

Here are a couple of pointers that snagged me the first time I dealt with linked servers:

• If the linked server is an instance, make sure you bracket the name. For example [SERVERNAME\INSTANCENAME].

• Use an alias for the table or view from the linked server or you will get a "multi-part identifier cannot be bound" error. There is a limit of a 4 part naming convention. For example SERVER.DATABASE.dbo.TABLE.FIELD has five parts and will give an error. However, SELECT linked.FieldName FROM SERVER.DATABASE.dbo.TABLE AS linked will work fine

Answer 2

You will want to link the servers:

http://msdn.microsoft.com/en-us/library/aa213778.aspx

Answer 3

for step 2 you need to have Distributed Transaction Coordinator running, you also need to use SET XACT_ABORT ON to make sure it will all rollback you also need to enable RPC which is turned off by default in 2005 and up

There is a whole bunch of stuff that can bite you in the neck