Adding linked server - SQL Server 2005 - Changing the name later

views:

201

answers:

Adding linked server - SQL Server 2005 - Changing the name later

Hello,

I'm developing a web app in a testing environment with test sql servers and databases. I'm currently adding a linked server and accessing the databases and tables in the linked server using [Servername].[DatabaseName].dbo.[TableName].

But after pushing it to production the DB servers name will change. So should i need to open each and every stored procedure again and change the server name?

What is the usual practice?

Thank you.

+1 A:

The link name need not be the same as the actual target server name. The easy solution is to use the same linked server name on both the development and production servers, just link to different actual servers. A development server might actually link to itself, for example.

RobC 2010-02-02 09:42:42

Oh, so the actual name points the provider name we added and not the original server name. Now i get it. Thank you.

NLV 2010-02-02 09:44:35

If i dont give the same name as the target server and try running some query i get the error "[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied."

NLV 2010-02-02 09:49:18

When you call sp_addlinkedserver to set up the linked server, pass in the local name for the @server parameter and the remote server name for the @datasrc parameter. See the documentation for sp_addlinkedserver for more details.

RobC 2010-02-02 10:04:35

I'm doing it through sql server management studio. Any ideas how to set up the name there?

NLV 2010-02-02 10:06:26

Right-click the server node at the very top of the Object Explorer tree, select `New Query`, then enter `sp_addlinkedserver @server='mylocalname', @srvproduct='', @provider='SQLOLEDB', @datasrc='myremotename'` and click the Execute button. (Replace "mylocalname" with the local name you want to use, and the same with "myremotename". "myremotename" can include a backslash if it is a named instance.)

RobC 2010-02-02 10:24:55

Thanks, it worked. I also set the remote login for the remote server using SP_addLinkedsrvlogin.

NLV 2010-02-02 10:36:38

ansaurus

tags:

views:

answers:

Adding linked server - SQL Server 2005 - Changing the name later

related questions