LINK SQL SERVERS IN THE SAME SESSION

Linked servers allow you to connect to a local server and to another 
SQL
Server in the same session. This scenario can be used to access data
using the linked server. However, in a development environment, you 
often
won't have two servers to emulate linked servers. This problem can be
solved if the linked server is defined as the local server. For 
example, let's
say ServerA is the local server and a linked server will be defined as
LinkSrvA with a database named TestDb. The following command will 
create a
linked server:

EXEC sp_addlinkedserver @server = 'LinkSrvA' , @srvproduct = ''
,@provider = 'SQLOLEDB' , @datasrc = 'ServerA' , @catalog = 'TestDb'

Here's a short description of each parameter used with
SP_ADDLINKEDSERVER:

* @server: the logical server name; not necessarily the physical name 
of
the server
* @srvproduct: a name you can give that's metadata about the provider
name, as in SQL Server OLE DB
* @provider: the provider name, as in OLE DB
* @datasrc: the data source, as in the database server name
* @catalog: the default database that you'll log in to

You'll need to add a login to the server (MyUser) and the database
(TestDb) to complete the ability to use the linked server.

Next, you'll need to add a linked server login. The following command 
is
the remote login that will be used when using the linked server,
LinkSrvA:

exec sp_addlinkedsrvlogin @rmtsrvname = 'LinkSrvA' ,@useself =
'false',  @locallogin = 'MyUser',@rmtuser = 'MyUser' , @rmtpassword = 
'password'

You can use the following command to test using the linked server:

select * from LinkSrvA.TestDb.dbo.sysobjects

However, there is a caveat to this setup. If you have a linked server
that's pointing back to the same server, you can't initiate a 
transaction
using that linked server.

When initiating a transaction using a linked server, you're essentially
creating a distribution transaction enlisting Microsoft Distributed
Transaction Coordinator (DTC). Microsoft DTC is a service installed 
with SQL
Server used for consistency between server transactions. When a
transaction is initiated on one server that involves a linked server, 
the DTC
service makes sure the transaction is committed or rolled back on each 
server.
For our setup, the DTC will prohibit the data from being updated on 
only
one server.

To generate the error, you will need to run the following command:

BEGIN TRAN
Select * from LinkSrvA.TestDb.dbo.sysobjects
COMMIT TRAN

You will receive the following error message:

Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator.]

For additional information regarding this issue, check out Microsoft
Knowledge Base article Q279857.
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q279857&

----------------------------------------