Linked Servers pointing to same server (localhost) causing "Transaction context in use by another session" error

Question

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.)

Answer 1

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

Answer 2

You'll need to configure multiple instances on your machine to make this work. Each machine that's a physical machine in production will need to be a new instance on your laptop. The reason for this is that linked server that reference the local machine don't work with distributed transactions.

And yes you will still need to have MS DTC setup and configured correctly to get all this to work with multiple instances on a single machine.