How to join tables in different databases

[harindaka]

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.

[iburyak]

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.E mployees[/PHP]

The same thing you use in joins.

Hope it helps.

[harindaka]

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...

[iburyak]

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_addlinkedser ver
'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.

[thedaver79]

harindaka

Guess i can refer to that table by [DatabaseName].[Owner].[TableName] in a join after all.

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.

[syamikm]

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

[yimma216]

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

[jamsoi]

any idea to make it happens in query designer 2005 ?