Hi group,

I am useing Linked Server in SQL Server 2008R2 connecting to a couple of Linked Servers.

I was able to connect Linked Servers, but I cannot point to a specific database in a Linked Server, also, I cannot rename Linked Server's name.

How to point the linked server to a specific database? How to rename the Linked Server?

The following is the code that I am using right now:

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
    @server = N'Machine123\Instance456',
    @srvproduct=N'SQL Server' ;
GO

EXEC sp_addlinkedsrvlogin 'Machine123\Instance456', 'false', NULL, 'username', 'password'  

IMHO, you have to use 4 part naming convention to get the data

you can refer a particular database using linked server like this

select * from [linkedserver].[linkedserverdatabase].dbo.[linkedservertable]

your linked server is -actually, name of the server\instance . so, you cannot rename directly.

may be(not sure) you can try by creating an alias on each client and use that. but this adds some confusion.

You can create a linked server like in the example below:

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver 
	@server = N'MYTESTLINK', 
	@srvproduct=N'MSSQL', 
	@provider=N'SQLNCLI11', 
	@datasrc=N'YourServer', 
	@provstr=N'PROVIDER=SQLOLEDB;SERVER=YourServer', 
	@catalog=N'YourDatabaseName';

You can also do this from the SSMS object explorer under Server Objects -> Linked Servers. Right click on it and select new linked server.

• Proposed as answer by Stan210 11 hours 48 minutes ago

You can create a linked server like in the example below:

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver 
	@server = N'MYTESTLINK', 
	@srvproduct=N'MSSQL', 
	@provider=N'SQLNCLI11', 
	@datasrc=N'YourServer', 
	@provstr=N'PROVIDER=SQLOLEDB;SERVER=YourServer', 
	@catalog=N'YourDatabaseName';

You can also do this from the SSMS object explorer under Server Objects -> Linked Servers. Right click on it and select new linked server.

good solution James. I only used this for data sources other than sql, 

You can set the default database for the linked server with the @catalog parameter that James showed you.

You cannot rename a linked server. You will need to drop and recreate it. Note that the linked server you define is just an alias. That is, the actual name of the remote server may be FRED, but you can call it WILMA if you like. In this case you need to specify @datasrc = 'FRED' in the call to sp_addlinkedserver.