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

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'




No comments:

Post a Comment

Dear User,

Thank you very much for your kind response