Add linked servers

--view linked servers



exec sp_linkedservers




--OR



select * from sys.servers where is_linked = 1



　

--add a linked server


EXEC sp_addlinkedserver

@server=N'STAGINGCONNECT',

@srvproduct=N'SQL Sever',

@provider=N'SQLNCLI',

@datasrc=N'DB7830F';



　

　

--view the linked servers and the logins to the linked server



select s.name, p.principal_id, l.remote_name

from sys.servers s

join sys.linked_logins l

on s.server_id = l.server_id

left join sys.server_principals p

on l.local_principal_id = p.principal_id

where s.is_linked = 1

Error: [SQLSTATE 42000] (Error 7391) OLE DB provider "SQLNCLI10" for linked server "" returned message "No transaction is active.". [SQLSTATE 01000] (Error 7412). The step failed.

I have a stored procedure in server2 that I was trying to call from server1 using linkedserver so that it gets executed in server2.
Server 1

• Windows Server 2008(x86)
• SQL Server 2008 (x86) Standard Edition SP1

Server 2

• Windows Server 2008 (x86)
• SQL Server 2008 (x86) Standard Edition SP1

Following  is the snippet of my code on Server1

SET  XACT_ABORT ON

BEGIN DISTRIBUTED TRANSACTION 
declare @RetVal int,

exec @RetVal = linksv1.db1.dbo.usp_update_Referrals 0

COMMIT TRANSACTION

So far I have taken the following steps

1. NETWORK CONNECTIVITY

• Ping server1 from server2  -- success
• Ping server2 from server1  -- success

2. DTC CONFIGURATION

• Open Start->Administrative Tools->Component Services
•  Expand the Component Services Node untill you find Local DTC right click and select properties option
•  In the Local DTC properties click in the security section and set the configuration as shown in the image

   Note that every time you make a change in the DTC Security configuration, the DTC service is restarted automatically

The final step is make shure the DTC service is started and configured to start when windows start.

3. DTC SERVICE IS STARTED

• Open Start - > Open Start->Administrative Tools->Services
• Find the Distributed Transactions Coordinator Service
• Make sure the status is “Started” and the Startup Type is “Automatic”.

Follow the same step in Server2

After the DTC is configured , checked the lnked servers
Linked Server Configuration on Server 1

 OKC is the linked server pointing to my Server2
Highlight the linked Server
Right Click and go to properties
Select the Server Option in the properties window and make sure the following settings are applied
Data Access: True
RPC: True
Enable Promotion of Distributed Transactions: True

All the settings were double checked ...will update in few weeks if we still get the "No transaction is active" error...