April 5, 2010 at 9:52 am
I have a several sprocs that need to use a linked server. The linked server will change between dev, test and prod (at least), so i would love to have an alias for the linked server. The following shows how to do that using gui tools:
http://alexpinsker.blogspot.com/2007/08/how-to-give-alias-to-sql-linked-server.html
I would much prefer to be able to script it since the linking will be done from multiple sites.
Possibly?
SQL Server version dependent?
Thanks.
April 5, 2010 at 2:13 pm
You can definitely script out the addition of linked servers as it sounds like you are wanting to do here.
As a very basic, just to add a linked server with an alias you would do something like this
EXEC sp_addlinkedserver @server = 'AliasName'
, @srvproduct=N'sql_server'
, @provider=N'SQLOLEDB'
, @datasrc=N'ActualServerName'
Obviously at this point you would have to add the serveroptions you want via sp_serveroption
and the security information you want via sp_addlinkedsrvlogin.
If you are in SSMS and you go through the New Linked Server screens, it gives you the ability to script out generally what you want by clicking the script button at the top of the screen. Once you script it out for one iteration you should be able to easily change it to suit each of your environments, assuming you want to keep the properties the same throughout.
Edited with correct parameters.
June 14, 2013 at 2:49 am
Hi all,
this would be extremely useful for a current project, however I'm a little concerned that there seems to be no official documentation for aliasing linked servers in this way...
Does any one know of any potential risk/problems with this method?
Many thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply