September 5, 2007 at 8:04 am
We have a number of stored procedures that include 4 part naming conventions to a linked server.
Recently we had a problem with the linked server and had to move the database to another server with a different server name. This meant going through each stored proc and changing the server name in the 4 part naming. Does anyone have any ideas of how I could avoid this in future by changing the stored procs so that if the server name changes we can make one change. I was looking to see if I could SQL2005 synonyms or maybe some form of global variable, but I don't think this is possible.
Any other ideas out there would be appreciated.
Thanks
September 5, 2007 at 10:35 am
Are you talking about in the text of the proc or in the actual proc name?
You can write a T-SQL statement that pulls the name & Text of the Procs affected into a Temp table, fix the Text column of the Temp table with an update statement, then run through a While loop or Cursor, doing an "Alter Procedure" statement for each name in the temp table and using the Text in the temp table as the update...
It's not one statement, but you could easily make it one job / script. Of course, the "Alter" part of the script would have to use dynamic SQL to update the procs, but other than that, it should be doable.
September 5, 2007 at 10:47 pm
Dazza -
Try an alias for your linked server. In a quick test I was able to create an alias, then a linked server pointed at "Server A" using the alias, no problem retrieving data. Then deleted both alias and linked server and recreated with the alias pointing at "Server B", no problem retrieving data...
At worst you might have to delete/recreate two settings rather than update a whole slew of procs, views, etc.
Joe
September 6, 2007 at 5:35 am
Thanks for both suggestions, the Alias suggestion seems to be the quickest and easiest method to solve this issue. I will test this solution out.
Many Thanks
September 6, 2007 at 10:33 am
The optimal solution is not for a client side alias but a DNS alias. With most applications a DNS alias to the FQDN of the SQL Server works great.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
September 6, 2007 at 10:51 am
Ahhh. Good one, Rudy.
Forget dynamic SQL. Use Rudy's suggestion. It'll save you having to change even the linked server connection / alias if the hardware changes.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply