We are using SQL Server 2008. Normally our app that is using different databases distributed over the network and some of the stored procedures we call therefore make use of linked servers.
Now I wanted to test our app locally and thus created all databases (that are normally distributed over the network) on my local machine. So all the databases are now living on "localhost". I changed the linked servers so that they are all pointing to localhost (and a quick select-test shows they are working).
However, when I am calling stored procedures that make use of the linked servers (that now point to localhost) and these procedures are using transactions, then I am getting the following error: "Transaction context in use by another session".
I am guessing this is because SQL Server thinks it's a distributed transaction, but it's really a local transaction?!
What are my chances of resolving this other than eliminating the connection servers on my local install and directly address the different database as they are now living on the same server?
If it's important: I didn't configure MS DTC, I just started the corresponding windows service on my machine. (Mostly because I wasn't sure where to solve this problem.)
Best Answer
Loopback linked servers cannot be used in a distributed transaction . Install two SQL Servers in local to solve your problem. Link one instance to another.
http://msdn.microsoft.com/en-us/library/ms188716(SQL.105).aspx