SQL Server Performance Forum – Threads Archive

Linked server as an alias

I am working with SQl 2K5 with sp1 and SQL 2K with sp4. Say our current SQL server setup as follows: ServerA (SQL 2k5)
ServerB (SQL 2K)
ServerC (SQL 2K5) I want to create a Linked server with the name, ServerC in ServerA and this linked server is actually linked to ServerB. Right now if I do that, no matter what I did, the linked server is still pointing to serverC. If this is not possible, here is what I try to solve. We have 100 views which hardcoded with four name part like, serverC.db1.dbo.table1. We want to test these 100 views on a test server. Since these view names has this four name part and it will take a lot of work to recode them. So that’s why I am thinking to create a linked server with serverC as its linked server name but actually point to serverB. If there is another way to do it, please advice. wingman

In the SQL Server Configuration Manager (2005) or Network Utility (2000) you can configure aliases under the "SQL Native Client Configuration" section.

You can do it through linked server alias… Right click the linked server and name the linked server as ServerC Click the "Other data source" radio button"
Click the drop down list and select the "Microst OLEDB provider for sql server"
In "Data Source" column type "ServerB" In security tap provide the credentials…
click OK…
MohammedU.
Moderator
SQL-Server-Performance.com

Yes, I did that just like what you described to the ServerA computer which is SQL 2k5. I used the local SQL login in ServerB for credential. When I run my select query, like select * from serverC.db1.dbo.table1 in ServerA, it still goes to ServerC not serverB. When I go back to the properties of the linked server alias I just created, all the setup is gone except it is still set to Microsoft OLEDB provider of SQL server in the provider drop down. wingman

quote:Originally posted by MohammedU You can do it through linked server alias… Right click the linked server and name the linked server as ServerC Click the "Other data source" radio button"
Click the drop down list and select the "Microst OLEDB provider for sql server"
In "Data Source" column type "ServerB" In security tap provide the credentials…
click OK…
MohammedU.
Moderator
SQL-Server-Performance.com

It is working fine for…
Check SQL Native Client Configuration… if there is any alias configured… MohammedU.
Moderator
SQL-Server-Performance.com

Thank for the quick response. Oh yes, thank for the suggestion. I didn’t think of it. It works. Now I have a follow-up question. In the four part naming scheme, the second part is the database name. Is there a way to alias the database name too? In other words, I want to do the exact same thing just like aliasing the server name, this means the db name, db1 in the four part name(ie. serverC.db1.dbo.table1 is actually pointing to db2 in serverB (the scenerao I described in my original email). Any creative idea is welcome. wing

quote:Originally posted by simondm In the SQL Server Configuration Manager (2005) or Network Utility (2000) you can configure aliases under the "SQL Native Client Configuration" section.

Check BOL topic "CREATE SYNONYM (Transact-SQL) " which may help you…. CREATE SYNONYM (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms177544.aspx
MohammedU.
Moderator
SQL-Server-Performance.com

Does it exist in SQL 2000? Wing

quote:Originally posted by MohammedU Check BOL topic "CREATE SYNONYM (Transact-SQL) " which may help you…. CREATE SYNONYM (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms177544.aspx
MohammedU.
Moderator
SQL-Server-Performance.com

No<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> And I don’t think there any other option available in 2000… MohammedU. Moderator SQL-Server-Performance.com

]]>