| There are no problems to add SQL linked server to the specific host running it. But what if you need to give it an alias, rather than use hostname as linked server name? Here is how to do it: 1) Step 1: • In SQL Server Management Studio open Linked Servers and then 'New Linked Server'. • Inside of appeared wizard – Select the General tab. • Specify alias name in "Linked server" field. • Select SQL Native Client as provider. • Add sql_server in "Product Name" field (that's the magic). • In "Data Source" – specify name of the host to be used as linked server. 2) Step 2: • In Security tab – specify proper security options (e.g. security context) 3) Step 3: • In Server Options tab – put "Data Access", RPC, "Rpc Out" and "Use Remote Collaboration" to be true. 4) Step 4: • Enjoy. |
Sunday, August 19, 2007
How to give alias to the SQL Linked Server
Subscribe to:
Post Comments (Atom)
57 comments:
It works! Thanks a lot!
Works great! Thanks!
yeah.. that's magic !
Thx!
Thank you!!
I'm glad it helped to many people. It's amazing that this piece of information is so concealed.
thanks!!!
Thanks for the info Alex, this is one of those changes that can make life much easier when migrating stored procs from Dev to Prod without having to rename links in the code.
I had to walk one of our DBAs through the process of setting up a link, over the phone, and without permission to access the linked server wizard. Not an easy task - is there any change you could add screen shots for future reference?
Thanks
S*
You saved me a ton of time.
Agito obviam astrum:
Move towards the stars??
Yes, move toward the stars, :)
yes, as someone commented, it helps to do it this way to make it easier to deploy stored procedures from dev to production without the need to change the path to the linked server insode stored procedures code.
thanks!
Excellent, I have servers with "-" in their names, that doesn't work to well in a query. This fixed that issue.
Thanks! This is just what I was looking for. I had to create a linked server in test and wanted it to have the same name as production without linking to the production server. Great post!
Perfect; exactly what I needed. Thanks a million.
Thanks thanks thanks. Very helpfull !!!
it works
it works
It works!
Thanks so much, it works pretty good
Hi,
I'd like to point out that this not a real alias.
Internal Features of SQL Server relay on SQL Server as product name, which you cant achive using this way. e.g. if you try to set it up with the product name SQL Server it will fail.
Issue an select * from sys.servers to compare
However, there is a supported way to alias a native SQL Server
EXEC sp_addserver 'AliasName';
EXEC sp_setnetname 'AliasName','ServerName'
And then administer the properties in the linked server dialog of SMMC.
Regards,
Kai
Hi Alex,
this , and also what Kay wrote, are almost what I need.
do you know if exist a way to make also an alias for the dbName?
thanks
GREAT!
Thanks a milllliooooon !! AWESOME solution.
Thanks a lot..You made my day.
cheers
Poorna
Thanks a lot for this 'clear' and 'concise' solution.
not that i have much to add, but thank you!
Many thanks for the guide, this information was especially important for me to learn how to give alias to the sql linked server. Many thanks
Worked like a charm
Worked perfectly and just what I was searching for. Thanks Very Much!
Wow... it worked perfectly... Thanks a ton... you are great..
I love that. great result.
Cool!
-Yaniv Etrogi
Unfotuantely its incorrect.
You should put "SQL Server", not "sql_server".
Thanks Alex
Great!!! I was looking for the solution, and could not find it!
THANKS!!!!
Sofya Nizhnaya.
Very Nice. Thank you so much.
You were a great help. It works great.
Just what I needed. Saved me a ton of work!
Thanks so much for sharing!
cool stuff yaar
Perfect. Thanks!
Sweet! Had forgotten about this, thanks!
Thank you, it works! :D
It works, thanks.
Why does it work?
What magic does 'sql_Server' do?
That works great! Thanks a lot.
Thanks very much, very useful for prod moves!!!!!!
WORKS!!!UR THE BESTTTTTTT
WORKS!!!
Thank you so much. It saved me. I wanted to run a distributed transaction and needed to explicitly address the remote server name placing special charactors like "." and "," . Thanks a lot. :-)
This still works for Sql Server 2012, thanks a bunch!
Thanks a ton Alex!! I want to know what's the magic behind sql_server! Also, I want to know if this method will make any difference in performance?
For connecting to SQL2008R2 ,
This Provider "SQL Server Native Client 11.0" wouldnt work.
I had to use the Provider "SQL Server Native Client 10.0"
Thanks Works Great now.
Thanks a lot!
/****** Object: LinkedServer [DB_ALIASNAME] Script Date: 12-06-2018 11:03:38 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'DB_ALIASNAME', @srvproduct=N'sql_server', @provider=N'SQLNCLI11', @datasrc=N'DB_ACTUALNAME'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DB_ALIASNAME',@useself=N'False',@locallogin=NULL,@rmtuser=N'USERNAME',@rmtpassword='########'
With the deprecation of SQL Native Client how would we do this now?
Thank you, this was most helpful
Thank you this was most helpful
Post a Comment