September 8, 2009 at 3:36 pm
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.
September 8, 2009 at 7:08 pm
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
September 8, 2009 at 9:07 pm
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.
September 9, 2009 at 5:37 am
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
September 9, 2009 at 10:33 pm
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
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply