Microsoft SQL Server 2008 R2
Problem :
Want to access stored procedure and table in another SQL Server
Solution :
1. Example SQL Server - 1 = SERVER_1
2. Example SQL Server - 2 = SERVER_2
3. Active at SERVER_1, then type this query to add New Linked Server,
--------------------------------------------------------
-----Code : Start
--------------------------------------------------------
USE Master
--Create Link Server
EXEC master.dbo.sp_addlinkedserver
@server = N'LINKED_2', --Link Server Name
@srvproduct = N'SQLServ', --it’s not a typo: it can’t be “SQLServer”
@provider = N'SQLOLEDB', --Provider Name
@datasrc = N'SERVER_2'; --Server Name
--Create Pair local and remote logins
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'LINKED_2', --Link Server Name
@useself = N'False',
@locallogin = N'sa', --Login user Local
@rmtuser = N'sa',
@rmtpassword = N'sasasa';
--For Executeble Command : exec SP
exec sp_serveroption @server='LINKED_2', @optname='rpc out', @optvalue='true'
--------------------------------------------------------
-----Code : End
--------------------------------------------------------
4.The Linked Server : "LINKED_2" already added successfully.
5.
Example, there is a table : "Orders" in SERVER_2
Next, try to access the table, from SERVER_1
--------------------------------------------------------
-----Code : Start
--------------------------------------------------------
select * from openquery
( LINKED_2, ' select * from Northwind.dbo.Orders ' )
--------------------------------------------------------
-----Code : End
--------------------------------------------------------
OR
--------------------------------------------------------
-----Code : Start
--------------------------------------------------------
select * into _Temp_Orders
from openquery
( LINKED_2, ' select * from Northwind.dbo.Orders ' )
--------------------------------------------------------
-----Code : End
--------------------------------------------------------
6.
Example, there is a Stored Procedure : "[Sales by Year]" in SERVER_2
Next, try to access the stored procedure from SERVER_1
--------------------------------------------------------
-----Code : Start
--------------------------------------------------------
exec ( ' exec Northwind.dbo.[Sales by Year]
''1/1/1996'',''1/31/1998''
') at LINKED_2 ;
--------------------------------------------------------
-----Code : End
--------------------------------------------------------
7. Optional, to remove the Linked Server
