Access Table A in DB1 from DB2

[whloo]

Hi,

I am quite new in this Sql Server administration.
I have 2 Database.
DB1 have list of tables and one of them is "User" table.
Another DB is DB2.
I am trying to access "User" table in DB1 from DB2 using view.
How can i do that?
Because in DB2 view, it only list down all tables in DB2.
Your help greatly appreciated.
Thanks!

Regards,
weihann.

 

[osjohnm]

Hi

You need to use the fully qualified name which consists of:
DATABASE.OWNER.TABLE eg.

select * from DB2.dbo.USER

Since want a view you can right click on Views in DB1 in Enterprise Manager > New View and you can build your query there and save it as User.

Alternatively you can use query analyser and script it like so:

create view user
as
select * from DB2.dbo.user

Two things though, I used select * as an example but its faster to specify only the columns you need. Secondly I wouldn't call the view USER as user is a reserved keyword in sql server. Therefore i would maybe call it USERS instead.

Once your view is created you can query it in DB1 just like you do a standard table.

Hope this helps

John