SQL Server 2005 : Access Tables / Entities across Servers.

Accessing objects (tables..) across servers (SQL Server) is very simple.

Step 1: Create a link between two servers

Goto Query Window

[sourcecode language=’sql’]
EXEC sp_addlinkedserver
   ‘PRODSVR’,
   N’SQL Server’
GO[/sourcecode]
‘PRODSVR’  is the name of the Server.Execute it.

Step 2: Setup Permission between Servers.In Management StudioGotoServer Object >> Linked Servers >>

PRODSVR (See Image)

Linked Servers List

Right Click on PRODSVR goto Properties and make changes as given below.

1-general.jpg

2-security.jpg

 3-securityoptions.jpg

Step 3: To access the tables in other server

Use the following  hirearchy

PRODSVR.dbname.dbo.tablename

To access Person.Address table from AdventureWorks database

[sourcecode language=’sql’]

Select * From PRODSVR.AdventureWorks.Person.Address

[/sourcecode]
Advantages :

1. Update Production server with data from development server. (SSIS is alternate way)

2. Allow users to access production database without multiple logins. (windows authentication or SQL authentication) 

kick it on DotNetKicks.com

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: