Click here to Skip to main content
Click here to Skip to main content

How to Config Linked Servers in a Minute

, 29 Apr 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
How to create Linked servers in a minute in an easy way
4.JPG

Introduction - How To Setup Linked Servers

If you want to use a database from another Server Instance in your queries, you should do some workaround to reach your goal, or better say if you have distributed databases for an application and you want to use distributed queries here is the simple, easy solution.

Background

Googling around would get you to this solution but it's important to know how much time you would spend on something. On this issue, it was so time consuming to find out how to really config the server to get it working, so now it's not.

Using the Code

This option is also available for SQL Server 2000. You should go to [Security]>[Linked Servers] tab under the connected SQL Server Instance.

I. Setting up Linked Server for Using as a NamedServer

  1. Connect to the specified DB Instance that you are going to use the Shared Server (Linked Server) in it.

    1.JPG

  2. Go to [Server Objects] and on the [Linked Servers] perform a [Right-Click] and select [New Linked Server...].
  3. In the new window on [General] page, you have to set several parameters as below:

    2.JPG

    1. [Linked server]: LLSS (The name that will be used for addressing the Shared server)
      SELECT * FROM [LLSS].[DBName].[Schema].[TableName] 
      
      SELECT * FROM [SERVER134].[CompanyDB].[dbo].[Employee] 
    2. [Server type]: Select [Other data source] Option
    3. [Provider]: [Choose SQL Native Client]
    4. [Product Name]: ZZZZZZZZZ (Shouldn't be empty and anything can be set, e.g. Instance Name)
      [ZZZZZZZZZ]
      
      [Server2005]
    5. [Data source]: XXX.XXX.XXX.XXX\DDSS (The Network name of SQL Server that is going to be shared on current Instance).
      [ XXX.XXX.XXX.XXX\DDSS]
      
      [192.168.100.134\Server2005]
    6. [Provider string]: (leave this parameter empty)
    7. [Location]: is disabled (leave this parameter empty)
    8. [Catalog]: the database Name (set your default database or leave it empty)
  4. Go to [Security] page, select [be made using this security context] option and set parameters as below:

    3.JPG

    1. [Remote login]: XX (the shared server login user name)
      [XX] 
      
      [sa] 
    2. [With Password]: YY (the shared server login password)
      [YY]
      
      [MyS@P@ss ]
  5. Press [OK ] and you are ready to go … and use the Linked Server as mentioned in part 3.1.

II. Setting up Linked Server for Using as a NetworkName

  1. Connect to specified DB Instance that you are going to use the Shared Server (Linked Server) in it.
  2. Go to [Server Objects] and on the [Linked Servers] perform a [Right-Click] and select [New Linked Server...].
  3. In the new window, you have to set several parameters as below:
    1. [ Linked server] : XXX.XXX.XXX.XXX\DDSS (The NetworkName of Shared Server that will be used for addressing the Shared server).
      SELECT * FROM [XXX.XXX.XXX.XXX\DDSS].[DBName].[Schema].[TableName]
      
      SELECT * FROM [192.168.100.134\SERVER2005].[ CompanyDB].[dbo].[Employee]
    2. [Server type] : Select [SQL Server] Option
  4. Go to [Security] page, select [be made using this security context] option and set parameters as below:
    1. [Remote login]: XX (the shared server login user name)
      [XX] 
      
      [sa] 
    2. [With Password]: YY (the shared server login password)
      [YY]
      
      [MyS@P@ss ]
  5. Press [OK ] and you are ready to go … and use the Linked Server as mentioned in part 3.1.

History

  • 29th April, 2009: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Kamran Hazari
Software Developer (Senior) Freelancer
Iran (Islamic Republic Of) Iran (Islamic Republic Of)
www.Kamran-Hazari.biz.ly
 
Freelance Software Developer

Comments and Discussions

 
GeneralThanks Pinmembermoeinmohebbi5-Feb-14 20:19 
QuestionThanks! PinmemberMember 469534013-Oct-13 23:20 
GeneralMy vote of 4 PinmemberSnehlata Shaw23-Feb-13 6:51 
GeneralMy vote of 5 Pinmembersuzman16-Jan-13 5:02 
QuestionError PinmemberAJMAL SHAHZAD2-Jul-12 22:16 
GeneralMy vote of 5 Pinmembermanoj kumar choubey17-Apr-12 3:30 
GeneralMy vote of 5 PinmemberKailash_Singh20-Jan-12 0:32 
GeneralMy vote of 3 PinmemberRoshanbin7-Jun-11 21:17 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.1411023.1 | Last Updated 29 Apr 2009
Article Copyright 2009 by Kamran Hazari
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid