How to Connect to SQL Server 2008 From 2005

Query multiple servers from one console using linked servers.

SQL Server 2008 was a minor database upgrade from the older SQL Server 2005 database engine. You link these two servers together using the "sp_linkedserver" stored procedure, which is available in SQL Server 2000, 2005 and 2008. This allows you to query your SQL Server 2008 server from your 2005 database. This eliminates the need to have multiple connections when you need to query multiple databases. You connect to only one server and query both from the same database connection.

Instructions

1. Click the Windows "Start" button and select "All Programs." Click the "SQL Server 2005" program group and then click "SQL Server Management Studio" from the list of shortcuts. This opens your database console screen.

2. Click your 2005 database name on the left side of the window. Click the "New Query" button to open your query window.

3. Type the following code into your query window:

   EXEC sp_addlinkedserver '2008Server','SQL Server';

   Replace "2008Server" with your SQL Server 2008 server name. The "N'SQL Server'" text tells the stored procedure to use the SQL Server drivers to connect.

4. Press the "F5" key to execute the statement and connect to the server. The new linked server name is displayed on the left of your window.

References

• Microsoft: sp_addlinkedserver (Transact-SQL)