Cross Server Inserts

Question

Cross Server Inserts

Rog Saber

SSCrazy

Points: 2548
More actions
September 8, 2009 at 3:36 pm

#134917

I have a trigger on a 2005 Server DB. This trigger calls a proc on another DB - 2000 which performs an insert.
I'll call 2000 server DB - 2000SVDB
I'll call 2005 server DB - 2005SVDB
When the trigger executes, I get:
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "2000SVDB" was unable to begin a distributed transaction.
OLE DB provider "SQLNCLI" for linked server "2000SVDB" returned message "The transaction manager has disabled its support for remote/network transactions.".
From 2005SVDB, I can update the table on 2000SVDB by calling the proc directly in a query window - like:
exec 2000serverDB.2000SVDB.dbo.proc_name
@parms
I only get the message above when calling the proc from the insert trigger on a table from 2005SVDB.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Paul White SSC Guru Points: 150467 More actions · Answer 1

The code inside a trigger runs inside an implicit transaction. You can look at this transaction as being the thing that protects the atomicity of the original statement. Say the trigger is fired by a single-row INSERT statement - the trigger becomes part of that INSERT so it must all commit or all rollback as one entity.

The implicit transaction is a local one, but when you try to call out to a remote server, SQL Server attempts to escalate it to a distributed transaction (again to make sure everything affected either all succeeds or all fails, as a unit).

Triggers are best (least bad?) when self-contained and lightweight. It is unusual to the point of perversity to attempt a cross-server call from trigger code. Can you tell us more about what you are looking to do? There will be a better way.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Rog Saber SSCrazy Points: 2548 More actions · Answer 2

More details...

I have an application that handles customer's requests to add users to be able to run our various software applications. To save time, I am adding functionality that allows our customers to put some values in a user specifics table automatically.

Example:

A customer makes a request to have their user 'Joe' set up to use our vehicle management software. When this request is made they can also make a request for the user to be defaulted to certain locations. When this request is created, I have a couple of tables that are populated.

user_request_table

This will contain something like add Joe to vehicle management application w/ a complete_flag = 'N'.

user_location_request_table

This will contain something like add Joe to the Chicago plant. Need to update the user_default_location table that resides on the customer's DB.

The guys who actually set up Joe to have access to the application have a process that they run that sets the complete_flag = 'Y' in the user_request_table when they complete te request. So, when this complete_flag gets set to 'Y', I created a trigger on the user_request_table to see if there is a corresponding value in the user_location_request_table. If there is I have to determine the server and DB in which the user_default_location table resides. I then call a proc on that server/DB and the proc performs the insert. I can't hardcode the server/DB name because it is different for each customer.

Hope this helps.

Paul White SSC Guru Points: 150467 More actions · Answer 3

Yikes Rog, that sounds like a bit of a nightmare.

Nevertheless, we all have to do stuff we'd rather not sometimes, so let's see...

Rather than try to do the work on a remote server from the trigger, write the details of what needs to be done to a permanent table.

You can then have a SQL Agent job comes along and do the real work.

I'm hoping that the advantages of doing it this way are apparent, but if not, just ask.

If we had a blank piece of paper for a new system design, I'd have a chat to you about the virtues of Service Broker too 🙂

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

Paul White (9/9/2009)
Yikes Rog, that sounds like a bit of a nightmare.
Nevertheless, we all have to do stuff we'd rather not sometimes, so let's see...
Rather than try to do the work on a remote server from the trigger, write the details of what needs to be done to a permanent table.
You can then have a SQL Agent job comes along and do the real work.
I'm hoping that the advantages of doing it this way are apparent, but if not, just ask.
If we had a blank piece of paper for a new system design, I'd have a chat to you about the virtues of Service Broker too 🙂
Paul

I agree... that will also keep the trigger and inserts to the table from failing if the other server happens to be down for maintenance or whatever. Critical inter-server dependencies that cause the sending server to fail it's operations when the receiving server is down are not a good thing no matter what their form.

Although I dislike Replication for many reasons, a better way to do this than the use of a trigger would be to use Replication.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)