Overblog Follow this blog
Edit post Administration Create my blog
Balavardhan Reddy Narani

Access one Database Server Object in another Database server

March 7 2009 , Written by Balavardhan Published on #SQL Server2005

Access one Database Server Object in another Database server

To access one DB Server from another DB server we required Linked Server object , whcih was created to access another DB Server
Below are the methods to Create Linked Servers

Required System stored Procedure "sp_addlinkedserver" Stored Procedure.

Ex : -- By Windows Authentication

EXEC sp_addlinkedserver @server= 'LinkedServer',
@provider='SQLNCLI', -- SQL Client
@datasrc='<< Provide DB Server IP>>',--the data source
@provstr='Integrated Security=SSPI;'

Ex : -- By Sql Authentication

exec sp_addlinkedserver
@server = 'LINK2',
@srvproduct = '',
@provider = 'MSDASQL',
@provstr = 'DRIVER={SQL Server};SERVER=<<serverip>>;UID=<<USERID>>;PWD=<<PASSWORD>>;'

After this you need to provide DB Server Authentication settings for Windows Login User
i) Create a Login For the Windows User
ii) Provide the sysadmin role in Server roles.
iii) Map the Database to that user to access in User Mapping.

 Write the Query to access the table in Second DB Server.

Select * from <<LinkedServer Name>>.<<Database Name>>.Dbo.<<TableName>>

Share this post

Repost 0

Comment on this post