Yes - it can link to multiple servers.
Perhaps this snippet from Books On Line will help:
Relational Database Engine Architecture Overview
Database Engine Components
The relational database server of Microsoft® SQL Server™ 2000 has two main parts: the relational engine and the storage engine. One of the most important architectural changes made in SQL Server version 7.0 was to strictly separate the relational and storage engine components within the server and to have them use the OLE DB API to communicate with each other.
So, in a sense, you have been using a linked server already.The local SQL server has the local relational engine “linked to” the local storage engine.
You can choose whether the linkage to the linked server is over ODBC or OLEDB.It depends which driver you use/how you define the datasource.
For each linked server in my care I have a folder with setup scripts and basic test scripts.Sample contents:
sp_linkedservergosp_tables_ex 'servername'gosp_columns_ex 'servername'goselect from a sample table using four part name
Remember that in query analyzer that the selected/highlighted area is executed (F5) not the whole script, so I can pick out the line(s) of interest or just let the whole thing rip.
Also, I set up a database with views so that an SQL query/script that will run in query analyzer on the target linked server host can run directly. Obviously, if the target linked server is not SQL server, then I have to be selective about what is included.
I like to have a table in each database just for test purposes. This is nothing fancy, couple of columns and three or four rows.
All in all, I can quickly verify if everything is working.Alternatively, I have actual proven syntax in front of me if something is wrong and my in the middle of the night mental table name, database name and syntax index search isn't quite functioning properly.
On my PC I have a test database that is mirrored in MS Excel, MS Access, MS SQL server 2000, MS SQL Server 2005, DB2 9, MySQL, Oracle 10g, Sybase ASE 15 and Unisys ClearPath DMSII 51.1.
Both the SQL Server 2000 and SQL Server 2005 have these databases as linked servers.
The setup is scripted and so is the basic functionality testing.
Can anyone tell me if you can link from SQL Server 2000 to 2005 (not 64 bit) and run queries from SQL Server 2000 against the 2005 server?
Good article, I learned something about OPENQUERY, which I had not used before.
A few things I have identified while doing linked server queries.
Thanks for the article, some useful stuff.
Use PubsSelect au_lname, au_fname from Authors-- Include owner:Select au_lname, au_fname from dbo.AuthorsUse Northwind-- Accessing table in a different database:Select au_lname, au_fname from Pubs.dbo.Authors
Select au_lname, au_fname from Lippman.Pubs.dbo.Authors
Create view Lippman_Pubs_dbo_Authors as Select au_lname, au_fname from Lippman.Pubs.dbo.Authorsgo
Select au_lname, au_fname from Lippman_Pubs_dbo_Authors
Select au_lname, au_fname from Lippman_Pubs.dbo.Authors