Loopback Server Policy

Hello all,

Recently, we ran into the issue that you can't do an insert into..exec statement on a loopback linked server that was previously commented on in:

http://www.dbnewsgroups.net/link.aspx?url=http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=124137&SiteID=1

So, for example, if you have a linkedserver to a database that happens to be on the same server as the querying thread, it fails with the message 'context in use'.

The answer from the previous thread was, don't use linked servers when the database is on the same server.

However,

The enviornment in our production system is fairly dynamic -- operations can be expected to move databases around in response to load balancing issues. We were counting on linked servers to make certain (non-performance sensitive) queries without regard to where a given database was located. Accepting that we have to make an exception case where the database lives on the same server means we'll have to have two sets of queries for every case this happens.

Something like

(pseudocode)

If server of linkedserver <> @.@.server

Insert into table....

Exec linkedserver.database.dbo.sproc

Else

Insert Into Table

exec database.dbo.sproc

(end pseudocode)

This seems pretty kludgy to me -- any suggestions on how to better manage this situation?

Thanks in advance

use cluster services instead of linked server