none
Transaction context in use by another session in Sql Server 2008 RRS feed

  • Question

  • Hi All,

     

    I have a stored procedure that uses SYNONYMS to insert and get data from (It´s a SQL Server 2008).

    If a point the ASP.NET application to my local database, it seems to works fine. But, if I point to Development Enviroment, the error that appear is: "Transaction context in use by another session."

    The reason why I can execute succesfully on my machine, but not in Development Enviroment, makes me think that is a server configuration issue.

    Does any one know what configuration should I apply in Development Enviroment that makes my application works?

    I´ve read some post on this forums, and all seems to be of SQL Server 2000/2005. Nothing with SQL Server 2008...

     

    Thanks in advance!!

    Tuesday, April 13, 2010 11:49 AM

Answers

All replies

  • Restart the sql server service, this will solve your prblem
    Tuesday, April 13, 2010 12:16 PM
  • Thanks Mayur, but Development Enviroment is being used by many pepople, so I can´t restart easily...

    Furthermore, if we put the application on Production Enviroment and get the same error... we are not able to restart the service, so I would like to try anothe solution.

    Anyway, I´ll try to restart the service tonight, and test if the problem is solved.

     

    Any other possible solution?

     

    Tuesday, April 13, 2010 1:24 PM
  • I´ve search wich is the instruction that raise the error. Is this: 

    INSERT INTO SY_SYNONYM_NAME (FechaHora) VALUES (@FechaActual) where @FechaActual is a variable set above with a GETDATE().

    If I remove this instruction, the next insert over a Synonym raise the error again, as I suspected....

    Tuesday, April 13, 2010 2:49 PM
  • Well, I´m still working on that, and it´s getting me crazy:

     

    • Mayur, I´ve restarted the service, and the problem still continues.
    • I´ve reduced the problem to this script:

     

     

       begin transaction

        SELECT TOP 1 * FROM Table1;

        INSERT INTO Synonym1 (FechaHora) values (GETDATE())

    commit transaction

     

    • This scripts produces the error in both enviroments: local and development.
    • The error is the same: Msg 3910, Level 16, State 2, Line 1 Transaction context in use by another session. 
    • The problem does not appear if I get data (with a select) from the synonym

    begin transaction

        SELECT TOP 1 * FROM Table1;

        SELECT * FROM  Synonym1;

    commit transaction

    • I´ve tested the script with distributed transaction, but the error still continues: begin distributed transaction ....
    Please, some help!

     

     

    Wednesday, April 14, 2010 9:26 AM
  • Another point: if I remove the transaction, it works perfectly:

         SELECT TOP 1 * FROM Table1;

        INSERT INTO Synonym1 (FechaHora) values (GETDATE()) 

     

    Wednesday, April 14, 2010 10:56 AM
  • It seems to me that you get "loop back linked server" problem.

    Where your synonym1 is pointing to in your local server and in Development Environment?

    read this http://msdn.microsoft.com/en-us/library/ms188716.aspx

    Hope this will help you.

    • Marked as answer by Cuttycharls Thursday, April 15, 2010 8:01 AM
    Wednesday, April 14, 2010 12:03 PM
  • Hmm Thanks for the link Dmitry... it seems to be a Sql Server limitation then.

    I will do it in the old way... generating scripts and rename all the references to the other database :-(

     

    Thanks!!

    Thursday, April 15, 2010 8:04 AM
  • Try restart the MSDTC in application server.
    Thursday, September 12, 2013 9:02 PM