How to create the linked server for SQL Server 2008 where we have the database from 2000 and 2005

by

There are a few different ways that you can create a linked server in SQL Server you can use the GUI in SQL Server Management Studio or via a script.

Using the instructions on MSDN you can do the following:

  1. Click Start, click All Programs, click Microsoft SQL Server 2005 or Microsoft SQL Server 2008, and then click SQL Server Management Studio.

  2. In the Connect to Server dialog box, specify the name of the appropriate SQL Server, and then click Connect.

  3. In SQL Server Management Studio, double-click Server Objects, right-click Linked Servers, and then click New Linked Server.

  4. In the New Linked Server dialog box, on the General page, in Linked server, enter the full network name of the SQL Serveryou want to link to.

  5. Under Server type, click SQL Server.

  6. In the left pane of the New Linked Server dialog, under Select a page, choose Security.

  7. You will need to map a local server login to a remote server login. On the right side of the Security page, click the Add button.

  8. Under Local Login, select a local login account to connect to the remote server. Check Impersonate if the local login also exists on the remote server. Alternatively, if the local login will be mapped to a remote SQL Server login you must supply the Remote User name and Remote Password for the remote server login.

  9. In the left pane of the New Linked Server dialog, under Select a page, choose Server Options. Set the Rpc and Rpc Out parameters to True, and then click OK.

An alternate way would be to use Transact SQL to write the query to set up the server using the stored procedure sp_addlinkedserver

EXEC sp_addlinkedserver   
   @server="yourServer", 
   @srvproduct="",
   @provider="SQLNCLI", 
   @datasrc="https://stackoverflow.com/questions/10402197/yourServer\instance1";

Either version will set up the linked server that you can then reference in your code.

More Related Contents:

  1. Regular Expressions in SQL Server servers?
  2. Check if table exists in SQL Server
  3. SQL Server query to find all permissions/access for all users in a database
  4. What is the maximum characters for the NVARCHAR(MAX)? [duplicate]
  5. Why use the INCLUDE clause when creating an index?
  6. SQL Server sp_msforeachtable usage to select only those tables which meet some condition
  7. Is it possible to force row level locking in SQL Server?
  8. SQL Server GUID sort algorithm. Why?
  9. What happens to the primary key Id when it goes over the limit?
  10. Drop primary key using script in SQL Server database [duplicate]
  11. SQL Server recursive query
  12. nvarchar(max) vs NText
  13. INFORMATION_SCHEMA vs sysobjects
  14. “Prevent saving changes that require the table to be re-created” negative effects
  15. What are row, page and table locks? And when they are acquired?
  16. Table Valued Function Killing My Query Performance
  17. How to add Active Directory user group as login in SQL Server
  18. best way to convert and validate a date string
  19. How to find current transaction level?
  20. An attempt was made to access a socket in a way forbidden by its access permissions
  21. Should every User Table have a Clustered Index?
  22. Aggregate bitwise-OR in a subquery
  23. How to set monday as first day of week in SQL Server
  24. Check if a temporary table exists and delete if it exists before creating a temporary table
  25. How to find SQL Server running port?
  26. SQL Server 2008 – How do i return a User-Defined Table Type from a Table-Valued Function?
  27. Check if a string contains a substring in SQL Server 2005, using a stored procedure
  28. Database design: Calculating the Account Balance
  29. Does query plan optimizer works well with joined/filtered table-valued functions?
  30. In SQL Server, how can I find everywhere a column is referenced?

Leave a Comment