How to join tables in different databases

Guys, heres my problem.

I'm developing a CRM system for the place i work and in that MS SQL Server database i have an Employees table. Now the thing is this table can and will be used for new systems which will be built in the future. So i think it is appropriate to have it in a seperate database rather than creating an Employees table in each and every database used for those systems.
Thats when the problem arises. Having the employees table in a different database prevents me from JOINING its data with other tables in other databases using queries in the normal way. Is there a way to overcome this? Or am i going about it in a wrong way? Thanks in advance for ur help.

re: How to join tables in different databases

If it is on the same Server just prefix table name with database name and owner.

Assume you have Database1 and Database2. Table Employees is in Database1


From Database2 do following:


[PHP]Select * from Database1.dbo.Employees[/PHP]

The same thing you use in joins.

Hope it helps.

re: How to join tables in different databases

Thanks man. Guess i can refer to that table by [DatabaseName].[Owner].[TableName] in a join after all. But i also came to know that u can do that only if the two databases reside in the same SQL server. If not i may have to implement link-servers (or whatever hmm....). How exactly in MS SQL Server 2005 do u do that? Please explain...

re: How to join tables in different databases

I thought databases are on the same server.
To link a server, follow this example from help. It should be done once.

[PHP]A. Use the Microsoft OLE DB Provider for SQL Server
Creating a linked server using OLE DB for SQL Server
This example creates a linked server named SEATTLESales that uses the Microsoft OLE DB Provider for SQL Server.

USE master
GO
EXEC sp_addlinkedserver
'SEATTLESales',
N'SQL Server'
GO[/PHP]


After servers are linked you can reference table on another server like:

[Server name].[database name].[owner].table_name


There are other ways to reference table on another without actually linking servers. But if your business doesn't have objections for linking it is an easiest approach.

Good Luck.

re: How to join tables in different databases

harindaka
Thank you for rephrasing the answer that iburyak posted. It made it very easy for a newbie like me to understand. The Brackets is what helped when my database name had a space in it. You both were very helpful.

re: How to join tables in different databases

This topic was very much useful for me also... thanks...

re: How to join tables in different databases

Thanks, I have been looking for the solution for a while too.

re: How to join tables in different databases

any idea to make it happens in query designer 2005 ?