April 25, 2018 at 8:21 am
Hello there,
I got a question related to the SQL Server Linked Servers.
I got the next scenario:
1 Instance
2 Databases
1 Linked server
The 2nd database have some tables and these tables have triggers, after insert, update or delete the trigger makes sure that the same record is updated, inserted or deleted into the table in the 1st Database.
But these triggers actually use the Linked servers to reference to the first database like this.
LINKEDSERVER.DATABASE.SCHEMA.TABLE
I know that if both databases are in the same instance there's no need to use the linked servers, but there actually are tons of triggers using them.
Whenever i activate a trigger i get the next error.
Msg 3910, Level 16, State 2, Line 112
Transaction context in use by another session.
Whenever i remove the linked server from the triggers everything works fine, but i can't go through every table removing these.
I've read in this blog the next.
The reason for the failure is that “Distributed Transactions”, the underlying infrastructure that co-ordinates transaction management only works when objects referenced via 4 part names are actually remote.
Is this true?
If that's true the only thing i have left is to modify the triggers of the tables?
Any ideas?
Thank you!
April 25, 2018 at 9:19 am
Superwaffle - Wednesday, April 25, 2018 8:21 AMHello there,I got a question related to the SQL Server Linked Servers.
I got the next scenario:
1 Instance
2 Databases
1 Linked serverThe 2nd database have some tables and these tables have triggers, after insert, update or delete the trigger makes sure that the same record is updated, inserted or deleted into the table in the 1st Database.
But these triggers actually use the Linked servers to reference to the first database like this.
LINKEDSERVER.DATABASE.SCHEMA.TABLEI know that if both databases are in the same instance there's no need to use the linked servers, but there actually are tons of triggers using them.
Whenever i activate a trigger i get the next error.
Msg 3910, Level 16, State 2, Line 112
Transaction context in use by another session.Whenever i remove the linked server from the triggers everything works fine, but i can't go through every table removing these.
I've read in this blog the next.
The reason for the failure is that “Distributed Transactionsâ€, the underlying infrastructure that co-ordinates transaction management only works when objects referenced via 4 part names are actually remote.
Is this true?
If that's true the only thing i have left is to modify the triggers of the tables?Any ideas?
Thank you!
That is correct. It's also explained in one of the Microsoft docs that the article links to. Refer to the last paragraph in this documentation:
Linked Server Considerations in a Clustered SQL Server
Sue
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply