Create view with data from multiple servers

Hi everyone,

I have 5 servers, all with identical databases just different data. I
have a rather lengthy SQL statement (in a View) to hit one database and
pull-in certain data, but I'd like to somehow run this same SQL
statement within the view but hit all 5 servers so we don't have 5
different versions of this data to mess with.

I'm not opposed to creating an update query in a stored procedure to
hit all 5 databases and update a table or even do this within a DTS,
but I'd prefer to keep it as simple as possible and as dynamic so the
users can simply run the view and get live data anytime based on all 5
tables.

Is this possible ?

Thanks,

rlanglySee "Creating a Partitioned View" in BOL.

AMB

--
Message posted via http://www.sqlmonster.com|||Ringo Langly (rlangly@.gmail.com) writes:
> I have 5 servers, all with identical databases just different data. I
> have a rather lengthy SQL statement (in a View) to hit one database and
> pull-in certain data, but I'd like to somehow run this same SQL
> statement within the view but hit all 5 servers so we don't have 5
> different versions of this data to mess with.
> I'm not opposed to creating an update query in a stored procedure to
> hit all 5 databases and update a table or even do this within a DTS,
> but I'd prefer to keep it as simple as possible and as dynamic so the
> users can simply run the view and get live data anytime based on all 5
> tables.

While you could set up a distributed partitioned view of the servers,
as suggested by Alejandro, but distributed partitioned views is
not really for the faint of heart. Anyway, it requires that the
tables has a partitioning column.

You can still up a view over the tables with UNION ALL, but the view
may not be updatable, and it sounds like you want to do this for an
update operation, but I might be misunderstanding something.

It's a little difficult to say that much more with that small amount
of information.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp