Linked Server Creation
Through Linked Server, you can connect to any other database or SQL Server database in other server and retrieve or modify the data of remote database.
Open the Enterprise Manager in SQL Server. Go to Security->Linked Servers.
Right click and select New Linked Server. Select SQL Server radio button.
Give network name of SQL Server in Linked Server textbox. Select the Security tab. Select ‘Be made using this security context’.
Give sa in Remote Login textbox and keep password blank. Note:- This should be the login and password to connect to remote server.
Click OK. It will create the Linked Server for remote server.
Note:- All the tables and views in remote server won’t be listed under Tables and Views under Linked Server. You can’t directly view the data of tables and views listed under linked server.
Linked server can also be added through sp_addlinkedserver system stored procedure.
Accessing data from Linked Server
You can access all the tables and views of all databases of Linked server.
To query the database in Linked Server, run the query as follows in SQL Query Analyzer:
Accessing data from Linked Server through Stored Procedure
If you would like to access or modify the data of Linked Server through Stored Procedure then you must set both ANSI_NULLS and ANSI_WARNINGS to on. Once you access or modify the data, set both ANSI_NULLS and ANSI_WARNINGS to off. Your Store Procedure should be similar to this:
CREATE PROCEDURE GetFoodLeisure AS
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SET ANSI_WARNINGS OFF