Transaction context in use by another session

One of causes of this situation is a trigger that works for linked-sever database table. An also SQL version of SQL-Server which processes database matters. To avoid this ERROR during sql query execution we should temporarily disable and after execution enable triggers for tables updated. All with database name check. Here is an example:

     Select * From People  where PersonId In (@PersonId, @PersonIdRight)
     IF 'DOUBLE' = DB_NAME()
        ALTER TABLE [dbo].[PeopleSites] DISABLE TRIGGER [PeopleSites_ENTDB_UPDATE]

     Update PeopleSites Set PersonId = @PersonIdRight Where  PersonId = @PersonId

     IF 'DOUBLE' = DB_NAME()
        ALTER TABLE [dbo].[PeopleSites] ENABLE TRIGGER [PeopleSites_ENTDB_UPDATE]


     Select * From PeopleSites where PersonId In (@PersonId, @PersonIdRight)

I solve It. I was using the same linked server to call the second procedure and then into the procedure I was using the same linked server.

It's very Easy, only we have to know the restricctions of linked servers.

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

I have resolved it by removing linked server used in the stored procedure and then called stored procedure by the same linked server. It wasnt working in DEV environement.