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

Question

Currently I am working on SQL Server 2000,2005 & 2008, my requirement is like, the database available in SQL Server 2000 & 2005 will be available in 2008 using a linked server.

Let's say I have a database in SQL Server 2000 called LIVE_2000 and in SQL Server 2005 it's called LIVE_2005, can someone please help me to create the linked server for LIVE_2000 and LIVE_2005 into SQL Server 2008?

1st thing is this even possible?

Thanks in advance...`

Solution

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='yourServer\instance1';

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

Comments

[Vikrant More]

ok but is this possible what i was trying i mean creating Linked server for 2000 and 2005 on 2008.

[Taryn]

I don't have access to test this, so you would have to follow the instructions to see if you can create one for 2000 and 2005 from sql server 2008

[Vikrant More]

yes i tried it i had just created 2000 on 2005 now i am trying 2000 & 2005 on 2008 Thanks!

[Andy K]

@bluefeet just brilliant! Thanks!