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
(@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 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.
