Thursday, 2 January 2020

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) is one such option that handles in 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 of the MARS concept, let’s check on the fix we have for this respective error.

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












2. I will be creating another table as "Doctors" and add 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 
(@datasrc).

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

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 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 insert works without any issues.

insert into hospital
values (7,'Hospitals7')
























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