Fix - ‘Transaction context in use by another session’ in SQL Server

The error – “Transaction context in use by another session” when comes up is pretty much straight of what’s happening at the backend. But, sometimes, the error can confuse some of the beginners that it could be coming because of a session already being executed in the same context of their query.

The error comes up when a single connection to SQL Server is trying for multiple batches of result sets. Multiple Active Result Sets (MARS) are one such option that handles SSMS. By default, the MARS option will be set to false and there is no direct option to view and enable them. 

Instead of getting in-depth into the MARS concept, let’s check on the fix we have for this respective error.

1. I have created a table "Hospital" which has some random values in them.

2. I will be creating another table as "Doctors" and adding some random values there as well.

3. Next, I will be creating an Insert trigger “Hospital_ITrig” on the hospital table. This Insert Trigger is added with a “link1” linked server that points to the same server. In our case, we have SQLArenaVMachine as our server product (@srvproduct) as well as the data source 

create trigger hospital_ITrig
on hospital
for insert
insert into dba_maint_testing.dbo.doctors
values (7,'Oasis','John','7')

EXEC master.dbo.sp_addlinkedserver @server = N'LINK1', @srvproduct=N'SQLArenaVMachine', @provider=N'SQLNCLI', @datasrc=N'SQLArenaVMachine', @catalog=N'master'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LINK1',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

4. I am going to insert it in the hospital table now and check the result. We will get the error as below.

5. Now, try to remove the “link1” linked server from the trigger code. If you notice that the insert works without any issues.

insert into hospital
values (7,'Hospitals7')

Note: Always make sure that there are no loopback-linked servers created that are underlying in your trigger script. However, this option of loopback linked server has become a legacy in the latest version of SQL Server.