Creating a partitioned view based on results from select?

I have a situation which seems like it should have a fairly simple
answer... but I'm not sure how to do it.
I want to create a view that joins several remotely distributed tables.
Something like:
CREATE VIEW AllData
AS
SELECT * FROM Server1.SomeDB.dbo.DataTable
UNION ALL
SELECT * FROM Server2.SomeDB.dbo.DataTable
...
SELECT * FROM ServerN.SomeDB.dbo.DataTable
The problem is that we anticipate some downtime or discontinuity with
our server links. Will the view fail if just ONE server can't be
reached? I think it will... please correct me if I'm wrong.
So, to solve this we have another table which stores the server name
and a status field. The question is how do I take this list of
available servers and turn it into a valid view? Is this going to
require some form of dynamic SQL? I'm picturing creating a trigger so
that any time the status DB changes the view gets recreated.status table could be out of sync and you will still get error. Long story
short, there isn't anything builtin right now to allow you precheck for
linked server status other than sending a query to it.
-oj
<bryanp10@.hotmail.com> wrote in message
news:1113511868.193053.138020@.l41g2000cwc.googlegroups.com...
>I have a situation which seems like it should have a fairly simple
> answer... but I'm not sure how to do it.
> I want to create a view that joins several remotely distributed tables.
> Something like:
> CREATE VIEW AllData
> AS
> SELECT * FROM Server1.SomeDB.dbo.DataTable
> UNION ALL
> SELECT * FROM Server2.SomeDB.dbo.DataTable
> ...
> SELECT * FROM ServerN.SomeDB.dbo.DataTable
>
> The problem is that we anticipate some downtime or discontinuity with
> our server links. Will the view fail if just ONE server can't be
> reached? I think it will... please correct me if I'm wrong.
> So, to solve this we have another table which stores the server name
> and a status field. The question is how do I take this list of
> available servers and turn it into a valid view? Is this going to
> require some form of dynamic SQL? I'm picturing creating a trigger so
> that any time the status DB changes the view gets recreated.
>|||OK... not quite what I was asking, but thanks. :)
Basically, I'm useless in T-SQL and what I really need to know is how
to transform a table of server names into:
select * from server1
UNION ALL
select * from server2
...
etc.
Really this is just a basic T-SQL question. In ANY other programming
language I've ever seen, this would be simplicity itself. But for some
reason T-SQL just baffles me.
The other question was whether this absolutely requires dynamic SQL or
not. At this point I'm thinking I might just build the create view
procedure in my app where I have a real language to do it.