|
Home > Archive > microsoft.public.sqlserver.server > November 2002 > Views w/ cross server selects via linked servers
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Views w/ cross server selects via linked servers
|
|
| Bruce de Freitas 2002-11-20, 3:23 pm |
| In a case where you have Views, and they do cross server
Selects... The method being done on this application I'm
working with, is to use a linked server name... but that
means the server name gets hard-coded in the View DDL.
like so...
select * from ServerA.pubs.dbo.authors
besides hard-coding of the server name, if you have a test
environment with different server names, you'd need to
change the server reference in all DDL, between all test
servers...
I was thinking of doing this instead. Any opinions or
commets are welcome.
Create a server alias, e.g. On ServerB "Books" would be
the alias for ServerA. Then doing a linked server using
the name "Books", instead of teh actual SQL name of
ServerA. The VIEW DDL would all need to change, across
all test/prod levels to look like this...
select * from Books.pubs.dbo.authors
Is this a typical way to deal with cross-server
references? Any better ways? Thanks, Bruce
| |
| Keith Kratochvil 2002-11-20, 3:23 pm |
| The alias sounds like a pretty good solution.
--
Keith, SQL Server MVP
"Bruce de Freitas" <bruce@defreitas.com> wrote in message
news:c7c601c290d4$bcfbcbc0$89f
82ecf@TK2MSFTNGXA01...
> In a case where you have Views, and they do cross server
> Selects... The method being done on this application I'm
> working with, is to use a linked server name... but that
> means the server name gets hard-coded in the View DDL.
> like so...
>
> select * from ServerA.pubs.dbo.authors
>
> besides hard-coding of the server name, if you have a test
> environment with different server names, you'd need to
> change the server reference in all DDL, between all test
> servers...
>
> I was thinking of doing this instead. Any opinions or
> commets are welcome.
>
> Create a server alias, e.g. On ServerB "Books" would be
> the alias for ServerA. Then doing a linked server using
> the name "Books", instead of teh actual SQL name of
> ServerA. The VIEW DDL would all need to change, across
> all test/prod levels to look like this...
>
> select * from Books.pubs.dbo.authors
>
> Is this a typical way to deal with cross-server
> references? Any better ways? Thanks, Bruce
>
| |
| Bruce de Freitas 2002-11-20, 9:23 pm |
| Thanks Keith. Yea, I don't see another way, short of
making a Linked Server with ODBC connection, but that
would change more then what I'd want... Only wondering
how we can enforce that nobody zaps the server alias in
Client Network Utility... Maybe there's a way to test
for an existing alias, and if it doesn't exist, create it
in a batch command?!? Bruce
>-----Original Message-----
>The alias sounds like a pretty good solution.
>
>--
>Keith, SQL Server MVP
>
>
>"Bruce de Freitas" <bruce@defreitas.com> wrote in message
> news:c7c601c290d4$bcfbcbc0$89f
82ecf@TK2MSFTNGXA01...
>> In a case where you have Views, and they do cross
server
>> Selects... The method being done on this application
I'm
>> working with, is to use a linked server name... but
that
>> means the server name gets hard-coded in the View DDL.
>> like so...
>>
>> select * from ServerA.pubs.dbo.authors
>>
>> besides hard-coding of the server name, if you have a
test
>> environment with different server names, you'd need to
>> change the server reference in all DDL, between all
test
>> servers...
>>
>> I was thinking of doing this instead. Any opinions or
>> commets are welcome.
>>
>> Create a server alias, e.g. On ServerB "Books" would be
>> the alias for ServerA. Then doing a linked server
using
>> the name "Books", instead of teh actual SQL name of
>> ServerA. The VIEW DDL would all need to change, across
>> all test/prod levels to look like this...
>>
>> select * from Books.pubs.dbo.authors
>>
>> Is this a typical way to deal with cross-server
>> references? Any better ways? Thanks, Bruce
>>
>
>
>.
>
|
|
|
|
|