Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

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

share|improve this answer
    
Thanks for your answer! –  DotNetDeveloper May 27 '13 at 8:35
add comment

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.

share|improve this answer
    
Thanks for your answer! –  DotNetDeveloper May 27 '13 at 8:35
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.