Link MS SQL Server 2000 Tables

[LonnieT]

Hello All,

I am new to this forum and hope that some one can help me with this problem.

I know that in MS Access you can have a linked table in your databse such that as long a the like is not broken, it behaves similar to any other table in the database.



My problem is that I need to do the same thing for multiple databases that I have on a MS SQL Server that ALL currently have copies of the same table in common.

I wish to have a single table that they all share and have access to just link any other table that they currently have but is actually linked.

Can this be done?

And if so, then could someone please guide me in this?

My logic is that if MS Access can link tables then I could suspect that MS SQL Server should be able to also, but I could be wrong.

Thanks in advance,
Lonnie

[rmiao]

Yes, you can do that with view. If table is on another sqlserver, you still can link it with linked server. Check sql books online for details.

[LonnieT]

Thanks for the reply,

can you please point me in the direction of some online sql book so that I can look up how to create these VIEWS?

Actually all of the databases are on the same server so that might make things a little easier as well.

Thanks again,
Lonnie

[LonnieT]

Actually one more thing to note.

Currently all of the databases are functioning with each having a copy of the table in each of them.

I would hope that if I replaced the table with a VIEW which is linked and thus shared that all of the existing SQL statements would not have to be modified as well, correct?

Thanks,
Lonnie

[rmiao]

Should be. Sql books online is installed on each sql server and sql client machine by default, you can find it in 'microsoft sql server' folder.

[™METAPeter_]

LonnieT,

Are you integrating data in accesss for analysis? If so, have you thought about using Analysis Services instead?

You question seems to hint at a common scenario for distributed analysis of data that might be easier implmented and MAINTAINED with analysis services.

Just a friendly 2¢ advice.

Peter Evans (__PETER Peter_)