I have a table called MyTable
on which I have defined a trigger, like so:
CREATE TRIGGER dbo.trg_Ins_MyTable
ON dbo.MyTable
FOR INSERT
AS
BEGIN
SET NOCOUNT ON;
insert SomeLinkedSrv.Catalog.dbo.OtherTable
(MyTableId, IsProcessing, ModifiedOn)
values (-1, 0, GETUTCDATE())
END
GO
Whenever I try to insert a row in MyTable
, I get this error message:
Msg 3910, Level 16, State 2, Line 1 Transaction context in use by another session.
I have SomeLinkedSrv
properly defined as a linked server (for example, select * from SomeLinkedSrv.Catalog.dbo.OtherTable
works just fine).
How can I avoid the error and successfully insert record+execute the trigger?
The transaction context is a pseudo-object that is passed to the server object from the client, or from one server object to another, in the case where one server object is invoking methods on another, and hence acting as its client. The transaction context carries the state of the transaction.
Basically Loopback linked servers are mainly useful when testing an application that uses distributed queries on a single server network. Note that they cannot be used in a distributed transactions.
Loopback linked servers can't be used in a distributed transaction if MARS is enabled.
Loopback linked servers cannot be used in a distributed transaction. Trying a distributed query against a loopback linked server from within a distributed transaction causes an error, such as error 3910: "[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction context in use by another session." This restriction does not apply when an INSERT...EXECUTE statement, issued by a connection that does not have multiple active result sets (MARS) enabled, executes against a loopback linked server. Note that the restriction still applies when MARS is enabled on a connection.
http://msdn.microsoft.com/en-us/library/ms188716(SQL.105).aspx
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With