Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

I'm new to doing linked server and I have this error.

"The linked server has been created but failed a connection"

On the same virtual machine I have two SQL Server instances

  • one 2000 (VMDEVCIAQ\NMP)
  • one 2005 (VMDEVCIAQ\NMPUTIL).

My setup :

  • The 2005 try to modify tables in the 2000.
  • I enabled remote connection on both instance.
  • I tested manually connecting to the instance of 2000 and it worked.

What I input in the form

  • Linked server: NMP
  • Provider : Microsoft OLE DB Provider for SQL Server
  • ProductName: SQLServer
  • Data source : VMDEVCIAQ\NMP

In the security tab I set "Be made using this security context" with the tested login and password.

So any idea why this would fail ? Any pointer or doc would be appreciated.

Cheers

share|improve this question
Usually there is something like "Additional Information", asuming you tried to create the linked server using the UI via Management Studio. Maybe you can post the details of the error. – Bernhard Kircher Feb 7 '12 at 19:39
1  
There is no SQL Server 2003 version - there are versions 6.5, 7, 2000, 2005, 2008, 2008 R2 and soon 2012. So what is your "old" SQL Server instance really?? – marc_s Feb 7 '12 at 21:35

migrated from stackoverflow.com Feb 7 '12 at 21:18

1 Answer

up vote 1 down vote

As one colleague pointed out, there's no SQL Server 2003 version. I suppose you're speaking about MSSQL 2000. Which was somehow related in time with VS 2003 :-).

As you're working with instances on the same server, do you have the service "SQL Server Browser" started and running?

Why you need it:

"The SQL Server Browser program runs as a Windows service. SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer. SQL Server Browser contributes to the following actions:

Browsing a list of available servers
Connecting to the correct server instance
Connecting to dedicated administrator connection (DAC) endpoints

"

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.