Alias a Linked Server - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Alias a Linked Server

Author

Topic

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2010-07-05 : 12:02:19

is there any way to either change the name, or alias a linked server permanately?

ie. i have a linked server (mssql) named:

myserver.domain.com\sqlexpress,1433

is there anyway to change the naem to: myserver ?

make sense?

Kristen
Test

22859 Posts

Posted - 2010-07-05 : 13:10:51

Just create another linked server with the same "target"?

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2010-07-05 : 14:22:35

ugh... what? not sure what you mean. sorry.

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2010-07-05 : 15:16:26

You can create a linked server using a script and specify any server name you want. You can then reference the linked server using that name instead of the full name you have above.

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2010-07-05 : 16:04:25

how do i name the server? i use the sp_addlinkedserver, but i dont see where you tell it, the name, instead of the host...

EXEC master.dbo.sp_addlinkedserver @server = myserver.mydomain.COM\SQLEXPRESS,1433', @srvproduct=N'SQL Server'

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2010-07-05 : 17:26:23

quote:
Originally posted by albertkohl

how do i name the server? i use the sp_addlinkedserver, but i dont see where you tell it, the name, instead of the host...

EXEC master.dbo.sp_addlinkedserver @server = myserver.mydomain.COM\SQLEXPRESS,1433', @srvproduct=N'SQL Server'

Here is an example I use:

Use master;

Declare @server nvarchar(50)
,@source nvarchar(50);

Set @server = 'myName'; -- Name of linked server to be created
Set @source = 'SourceSystem'; -- Source system to access

--===== Do the following, in case the server already exists
Execute master.dbo.sp_dropserver @server, 'droplogins';

--===== Create the linked server with our custom name
Execute master.dbo.sp_addlinkedserver
@server = @server
,@srvproduct = N'SQLServer OLEDB Provider'
,@provider = N'SQLNCLI'
,@datasrc = @source;

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2010-07-05 : 20:53:06

worked perfectly, thanks!

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2010-07-05 : 22:56:53

You are welcome - thanks for the feedback.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources