wilmott.com

Does anyone know if sql server 2000 allows a single query to inn-join tables which reside on two different database servers (not different databases, or tables, i.e. each has a unique server name and a workstation name) ?

If it doesn't them sql server isn't too smart. Don't expect the query to perform too well though. Any distributed query that requires a join will be slow as the machine driving the query is likely to know nothing of the remote database, so will only be able to use a sort/merge join rather than a more optimal one. Depending on what you want to do, you could consider replication of tables between the databases (not sure if SQL Server does this) or simply copying the data you need from the remote database into an indexed table on the local machine. Hope this helps.

QuoteOriginally posted by: yuanche17Does anyone know if sql server 2000 allows a single query to inn-join tables which reside on two different database servers (not different databases, or tables, i.e. each has a unique server name and a workstation name) ?The preferred way is to use Replication, ie set up Publish/Subscribe relationship between databases.This way you can access tables in the remote database as they where tables on the local. SQLServer handles the rest.

Mr Parker is quite right that replication is often the right way.However, you can write multi server queries in MS TSQL. using the OPENROWSET keyword.In essence you're using OLEDB to return rows, which Select can then join to your data. The documentation on this is quite adequate, but feel free to ask questions if you get stuck.The problem you may hit is that you are going to create locks on both databases whilst executing a query that by necessity runs quite slowly. This may mean that your code runs at an acceptable speed, but other users experience significant delays. Be aware that you many need SP3 for this to run correctly.

The great thing with the techniques is that you don't even need to rely on a SQL Server, it could be any db with OLE DB support. Watch out for locking, but that's a resposibility that comes with any system development of course.

In Oracle, it is quite easy to do this - Create a DB Link from one database to another. This can be done with SQL navigator or any GUI like TOAD,etc. Then use the tablename + DB link name within the query.eg.,There are 2 database - DB1 and DB2.Create a DBLINK on DB1, for connecting to DB2. Name this mydb_DB2.Then on DB1, run a query likeselect * from table1, table2@mydb_DB2where......I don't know the equivalent in SQL server, but I guess there could be similar thing with a different name.Hope it helps.Good luck!

you can creat a DB link in SQL server as well.