SQL Linked Server

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.

1. Open the Enterprise Manager in SQL Server. Go to Security->Linked Servers.

2. Right click and select New Linked Server. Select SQL Server radio button.

3. Give network name of SQL Server in Linked Server textbox. Select the Security tab. Select ‘Be made using this security context’.

4. Give sa in Remote Login textbox and keep password blank. Note:- This should be the login and password to connect to remote server.

5. 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.

 First register the Linked Server. Simple wizard would help you to register the server.

To query the database in Linked Server, run the query as follows in SQL Query Analyzer:

SELECT *

FROM

LNKSERVER.Food.dbo.Food_Leisure

Here LNKSERVER is the Linked Server name, Food is the database name, dbo is the default user and Food_Leisure is the table name.

 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

SELECT *

FROM


LNKSERVER.Food.dbo.Food_Leisure

 

 SET ANSI_NULLS OFF

SET ANSI_WARNINGS OFF

GO