Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sqldba20
Posting Yak Master
183 Posts |
 Posted - 2007-08-02 : 13:17:57
|
| Folks:We are using lots of stored procedures using cross server / linked server queries and the server names are hard coded in the stored procedures. What is the best way to use this linked server so that when we have to change server name we need not change it in ALL stored procedures but just in one stored procedure. Can we do it?Thanks ! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-08-02 : 13:30:30
|
| You don't have to use the server name in the linked server. It might be easier to just use aliases that way you don't need to update any stored procedures when you move servers. The only thing you would need to update is the alias on the server that has the linked server. An alias can be created in Client Network Utility. Whatever you decide to name it is what you would use for the linked server name.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
sqldba20
Posting Yak Master
183 Posts |
Posted - 2007-08-02 : 13:58:57
|
| Thanks Tara for your prompt reply !What I am asking is we want developers to use linked server based on our business data. What I would like to know is whether a server name can be stored in variable and then pass that variable? If so, can it be done as a global variable and how can we do it so that users will just have to type SERVER1.DBNAME..TABLENAME or SERVER2.DBNAME..TABLENAME or SERVER3.DBNAME..TABLENAME instead of the actual server name. The actual server names will be stored in some environment procedure. Any Help is appreciated... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-08-02 : 14:10:23
|
| You'd have to use dynamic SQL if you did it that way. Just use the alias though, it solves all of your problems!Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
Stored Procedures with Cross Server Queries