How to link 2 SQL Server 2000 databases 1

[Kobaia27]

Hi everyone,

I have a generic database and I want to add new tables which are specific to a project I am working on.

For this, I thought of creating a new database containing the new specific tables (which have relationships with other tables in the generic database). I therefore need to link these two databases together. I think it is possible with Oracle but can I do it with SQL Server 2000? and how?

Thanks

 

[TomasDill]

As far as I know, only by using triggers.
SQL Server commands allow to use 'DatabaseName..TableName.FieldName' syntax to access tables from another database.

However, this is not a good idea. It is much better to use a single database and just use special naming conventions for table in the database to separate one set of tables from another set. For example, for module 'Customer Tracking Information' table names could be prefixed by 'cti' to separate them from other tables.

Vlad Grynchyshyn
vgryn@softserve.lviv.ua

http://www.softservecom.com

The professional level of programmer could be determined by level of stupidity of his/her bugs

 

[tlbroadbent]

You cannot establish foreign key relationships between tables in different databases. However, you can JOIN tables in different databases. With SQL 7 and SQL 2000 you can even JOIN tables different databses on different servers.

Example: Join TableA on db1 to TableB on db2

Select a.col1, a.col2, b.col3
From db1.dbo.TableA As a
Inner Join db2.dbo.TableB As b
On a.col1=b.col6 Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[sguslan]

Hi

Why not think of creating the new project tables under the ownership of a different user, then all u have to do is manage the select/update/insert..etc permissions ....

Good luck