Atlanta Custom Software Development 

 
   Search        Code/Page
 

User Login
Email

Password

 

Forgot the Password?
Services
» Web Development
» Maintenance
» Data Integration/BI
» Information Management
Programming
  Database
Automation
OS/Networking
Graphics
Links
Tools
» Regular Expr Tester
» Free Tools

How to add/configure a new Linked server using T-SQL

Total Hit ( 7177)

Rate this article:     Poor     Excellent 

 Submit Your Question/Comment about this article

Rating


 


Here is the T-SQL script to add linked server using sp_addlinkedserver SP. Once you add linked server you can add login using sp_addlinkedsrvlogin. By default current login is mapped with linked server means remote machine must have exact same login with same password in order to get access to the linked server but this is not the case most of time so you need step [3].

Click here to copy the following block
USE MASTER
GO

--////////////////////////////////
--//[1] Create a linked server
--////////////////////////////////
Exec sp_addlinkedserver
   @server='webdb', --//Logical name given to the linked server.
   @srvproduct='', --//optional . Just for description
   @provider='SQLOLEDB', --//OLEDB Provider name, check BOL for more providers
   @datasrc='AMD64', --//actual remote server name
   @catalog='Northwind' --//default database for this linked server

GO
--////////////////////////////////
--//[2] Enable/disable some options
--////////////////////////////////
Exec sp_serveroption 'webdb', 'data access', 'true' --Enables and disables a linked server for distributed query access
Exec sp_serveroption 'webdb', 'rpc', 'true' --//Enables RPC from the given server.
Exec sp_serveroption 'webdb', 'rpc out', 'true' --//Enables RPC to the given server (required to call SP using Linked Server).
Exec sp_serveroption 'webdb', 'collation compatible', 'true'

GO

--////////////////////////////////
--//[3] Add linked server login
--////////////////////////////////
Exec sp_addlinkedsrvlogin
   @useself='false', --//false means we are going to use remote login/password
                    --//true means use local login/password to connect to remote machine (If local login/password does not match on remote machine then will fail)
                       
   @rmtsrvname='webdb', --//Exising Linked server name
   @rmtuser='remote_login' , --//remote login
   @rmtpassword='pass_of_remote_login' --//remote login's password

Now you can try to run the following query to test your SQL linked server

Click here to copy the following block
SELECT * FROM OPENQUERY(webdb, 'SELECT * FROM Orders')



Submitted By : Nayan Patel  (Member Since : 5/26/2004 12:23:06 PM)

Job Description : He is the moderator of this site and currently working as an independent consultant. He works with VB.net/ASP.net, SQL Server and other MS technologies. He is MCSD.net, MCDBA and MCSE. In his free time he likes to watch funny movies and doing oil painting.
View all (893) submissions by this author  (Birth Date : 7/14/1981 )


Home   |  Comment   |  Contact Us   |  Privacy Policy   |  Terms & Conditions   |  BlogsZappySys

© 2008 BinaryWorld LLC. All rights reserved.