2

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)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user2570651
  • 21
  • 1
  • 1
  • 2
  • Have you tried this links http://stackoverflow.com/questions/1362108/cannot-initialize-the-data-source-object-of-ole-db-provider-msdasql-for-linked and http://stackoverflow.com/questions/15441682/cannot-initialize-the-data-source-object-of-ole-db-provider-microsoft-ace-oledb – Edper Jul 11 '13 at 01:45

1 Answers1

4
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

RoMEoMusTDiE
  • 4,739
  • 1
  • 17
  • 26