Add Linked Server on Microsoft SQL Server 2005/2008
1.Connect SQL Server from Local Machine.
Click on Server Objects-->Linked Servers
2.Click on New Linked Server
3.Fill the values in it
--Linked Server:BLOG_LS
--Data source:BHAVESH\SQLEXPRESS
--Catalog:master(OPTIONAL Database Name)
4.Click on Security Page
Add Remote Username and password.This User must have sysadmin privileges.
5.Test Connect by Executing Query on Local Server.
select *from BLOG_LS.DBNAME.dbo(SCHEMANAME).TABLENAME
GO
Select *from openquery(BLOG_LS,'Select getdate()')
GO
GO
Select *from openquery(BLOG_LS,'Select getdate()')
GO
6.Execute this script to create it manually.
Change the Parameter according to your environment.
/******
Object: LinkedServer [BLOG_LS] Script Date: 05/15/2012 16:07:02 ******/
EXEC master.dbo.sp_addlinkedserver @server =
N'BLOG_LS',
@srvproduct=N'sql', @provider=N'SQLOLEDB', @datasrc=N'BHAVESH\SQLEXPRESS'
/* For security
reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'BLOG_LS',@useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @server=N'BLOG_LS', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'BLOG_LS', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'BLOG_LS', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'BLOG_LS', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'BLOG_LS', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'BLOG_LS', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'BLOG_LS', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'BLOG_LS', @optname=N'dpub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'BLOG_LS', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'BLOG_LS', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'BLOG_LS', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'BLOG_LS', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'BLOG_LS', @optname=N'use remote collation', @optvalue=N'true'
Comments
Post a Comment
Dear User,
Thank you very much for your kind response