Query data from a different database

Greetings,

Is it possible to create a view that selects data from a different database, SQL 2005?

i.e. Server A.Database 1.Table 1, Can Server B.Database 2.View1 be based on Server A.Database 1.Table 1

I thought I browsed by a page a few days ago that gave an example. Unfotunately I did not bookmark it.

Thank you,

Charles

I think you can do this directly with views, but if not you should be able to use synonyms as a go between to get it done. Here is the view example that I think works in SQL Server 2005.

sp_addlinkedserver 'otherserver', <other stuff specific to the server I set up>

go

-- using a view directly

create view v as select * from otherserver.<thedatabase>.<owner>.<table>

go

select * from v

go

-- I think that works, but if it does not work directly, then you could do this:

create synonym a for otherserver.<thedatabase>.<owner>.<table>

go

create view v as select * from a

go

The advantage of the synonym is that it is dynamically bound, so you could switch what it points to dynamically, the disadvantage of the synonym is that API metadata queries such as ODBC and OLE DB catalog metadata queries don't work well with them. They provide a lot of flexibility on the server side, but not as much on the client side. If you combine them with views, however, and use a view as the client access point instead of directly using the synonym, you shouldn't run into issues with catalog metadata since the client will be querying a view, not a synonym.

Hope that helps!

John

|||View over a synonym is not late bound, it is bound at the compile time.
So if you change your underlying table definition your view will stop working, with something like:
The OLE DB provider "SQLNCLI" for linked server "server2" reported a change in schema version between compile time ("168946857210586") and run time ("168946857240160") for table ""tempdb"."dbo"."t1"".