How To Setup Linked Servers in SQL Server

How to create Linked servers in a minute in an easy way

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.

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.
   
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:
   
   1. [Linked server]: LLSS (The name that will be used for addressing the Shared server)
      Hide   Copy Code

      SELECT * FROM [LLSS].[DBName].[Schema].[TableName] 
      
      SELECT * FROM [SERVERNAme].[CompanyDBName].[dbo].[CompanyMaster] 

   2. [Server type]: Select [Other data source] Option
   3. [Provider]: [Choose SQL Native Client]
   4. [Product Name]: BBBBBBBB (Shouldn't be empty and anything can be set, e.g. Instance Name)
      Hide   Copy Code

      [BBBBBBBB]
      
      [Server2005]

   5. [Data source]: XXX.XXX.XXX.XXX\DDSS (The Network name of SQL Server that is going to be shared on current Instance).
      Hide   Copy Code

      [ XXX.XXX.XXX.XXX\DDSS]
      
      [192.168.1.25\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:
   
   1. [Remote login]: XX (the shared server login user name)
      Hide   Copy Code

      [XX] 
      
      [sa] 

   2. [With Password]: YY (the shared server login password)
      Hide   Copy Code

      [YY]
      
      [MyS@P@ss ]

5. Press [OK ] and you are ready to go … and use the Linked Server as mentioned.