SQL Server 2008 linked server connection string setup

Question

I'm trying to setup a linked server to another instance of SQL Server installed on the same Windows Server. In the SQL Server Management Console I have both instances added and I'm trying to do a insert from one database into another. I setup the linked server using the query below and I'm getting the following failure message when I test the connection of the the linked server. Can someone help me solve this problem?

USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver 
   @server = 'Server Name',  --actual server name
   @srvproduct = '',
   @provider = 'MSDASQL',
   @provstr = 'DRIVER={SQL Server};SERVER=Database name;UID=test_user;PWD=test_pwd;'
GO

Error message

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "Server Name".
OLE DB provider "MSDASQL" for linked server "Server Name" returned message "[Microsoft] [ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).".
OLE DB provider "MSDASQL" for linked server "Server Name" returned message "[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.". (.Net SqlClient Data Provider)

Have you tried this links stackoverflow.com/questions/1362108/… and stackoverflow.com/questions/15441682/… — Edper

maSTAShuFu maSTAShuFu · Accepted Answer · 2013-07-11T01:53:41

exec sp_addlinkedserver @server='servername';
exec sp_addlinkedsrvlogin @rmtsrvname='servername',@useself=false, @rmtuser='sa',        @rmtpassword='Password';

if sql server is a named instance then servername\instancename

SQL Server 2008 linked server connection string setup

1 Answers