Setting SQL Servers to be able to Communicate Each Other

Followings are the basic steps if you want to write any stored procedures or DML(Data Manipulation Language) in an SQL servers that accesses remote SQL servers:

1. Add Linked Servers
To make a server be able to communicate with other servers we need to add linked servers by calling sp_addlinkedserver.
The syntax is as follow:
EXEC sp_addlinkedserver , N'SQL Server'

2. Setting Distributed Transaction Coordinator
Verify the "Distributed Transaction Coordinator" Service is running on both servers:
1. Go to "Administrative Tools > Services
2. Turn on the "Distribute Transaction Coordinator" Service if it is not running

If it is running, in both servers:
1. Go to "Administrative Tools > Component Services"
2. On the left navigation tree, go to "Component Services > Computers > My Computer"
3. Right click on "My Computer", select "Properties"
4. Select "MSDTC" tab
5. Click "Security Configuration"
6. Make sure you check "Network DTC Access", "Allow Remote Client", "Allow Inbound/Outbound" with ‘No Authentication Required’, "Enable TIP" and ‘Enable XA Transactions’
7. The service will restart
8. It might be needed to reboot server if there is issue on executing the SP.

3. Adding Remote Instance Name to the Host
In Windows Explorer, go to ‘C:\Windows\system32\drivers\etc’, open ‘hosts’ file. In bottom part add IP and remote instance names by typing:
[Tabkey]

By doing all above steps, you will be able to execute SP like in below sample where a server in Head Office accesses server in remote sites and collect the data back to Head Office (at least in my network it works):

CREATE PROCEDURE [dbo].[HSAP_SP_UPL_Test]
AS
BEGIN

DECLARE @ServerIP AS varchar(100)
SET @ServerIP = '[192.168.5.1].[MataPao].[dbo].[HSAP_SP_RPT_ItemClassSummary]'

DECLARE @hsap_EstateClassSummary Table (
MatGroup nvarchar(5),
MatDesc nvarchar(50),
Value decimal (18,0)
)

INSERT @hsap_EstateClassSummary
EXEC @ServerIP
@FromDate = '2009-11-01',
@ToDate = '2009-11-30'

SELECT * FROM @hsap_EstateClassSummary
END