x

SQL 2005 & 2008 R2 together on dev PC

I've installed 2008 R2 on my dev machine and now I want to get the R2 instance answering to the name (local). (I'm just not ready to uninstall 2005 yet just in case but quite a bit of our stuff uses (local) in the connection string for development purposes.) The SQL Browser service is running but connections to (local) aren't succeeding.

I keep getting this error:

A network-related or instance-specific error occurred while establishing a 
connection to SQL Server. The server was not found or was not accessible. 
Verify that the instance name is correct and that SQL Server is configured 
to allow remote connections. (provider: Named Pipes Provider, error: 40 - 
Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

I've tried:

  1. Stopped all the 2005 services.
  2. Set up an alias in R2 called (local) that points to my R2 instance - with and without the port number the instance is listening on.
  3. Set up an alias called in R2 that redirects to the R2 instance and another called (local) that redirects to .
  4. Started the 2005 service and set up an alias called (local) that redirects to the R2 instance.

I think part of the problem is that (local), localhost and . are special server names that trigger the SQL Client to connect via the Shared Memory protocol. Aliases only cover the TCP/IP, Named Pipes and VIA protocols. I suspect that Shared Memory doesn't do aliasing, but I'm hoping there's some way to con it.

Any ideas, please?

more ▼

asked Jun 24, 2010 at 06:28 AM in Default

David Wimbush gravatar image

David Wimbush
5.2k 29 31 34

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Assuming your machine is called MACHINE, and that the SQL instance MACHINE was the 2005 default instance, and now you have another instance called MACHINE\SQL2008R2, (I'm getting there....) can you not create an alias of MACHINE\SQL2008R2 called MACHINE?

more ▼

answered Jun 24, 2010 at 11:02 AM

Kev Riley gravatar image

Kev Riley ♦♦
54.3k 47 49 76

Nice try, Kev, I hadn't thought of that. Sadly, that doesn't work either.
Jun 24, 2010 at 12:18 PM David Wimbush
(comments are locked)
10|1200 characters needed characters left

Another answer, as this is a different approach.....

You seem to be one the right track about shared memory

MSDN/BOL:

Connections to Microsoft SQL Server from a client running on the same computer use the shared memory protocol. Shared memory has no configurable properties. Shared memory is always tried first, and cannot be moved from the top position of the Enabled Protocols list in the Client Protocols Properties list. The Shared Memory protocol can be disabled, which is useful when troubleshooting one of the other protocols.

You cannot create an alias using the shared memory protocol, but if shared memory is enabled, then connecting to the Database Engine by name, creates a shared memory connection. A shared memory connection string uses the format lpc:[\instancename].
...
...
The word localhost or a period (.) can be used in place of (local).

So why not try disabling Shared Memory, then your aliasing tricks might have a chance?

more ▼

answered Jun 25, 2010 at 08:15 AM

Kev Riley gravatar image

Kev Riley ♦♦
54.3k 47 49 76

Thanks, Kev. Sorry, I only just noticed you'd come back on this one! Another nice idea but still no luck. I think I'm going to just uninstal R2 and upgrade my 2005 instance in place instead. It's not a big deal now that I'm no longer finding any reason to use my 2005 instance or tools.
Jul 22, 2010 at 11:55 PM David Wimbush
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1951
x1853
x47

asked: Jun 24, 2010 at 06:28 AM

Seen: 1797 times

Last Updated: Jun 24, 2010 at 12:37 PM