Script(s)

what I learn is what u c

SQL Server 2005 : Access Tables / Entities across Servers.

leave a comment »

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

Step 1: Create a link between two servers

Goto Query Window

EXEC sp_addlinkedserver
   'PRODSVR',
   N'SQL Server'
GO

‘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


Select * From PRODSVR.AdventureWorks.Person.Address

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

About these ads

Written by gchandra

February 18, 2008 at 3:31 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: