开发者

Can I specify the default SQL Server with two named instances installed?

I have SQL 2005 Express installed. I then installed SQL Server 2008 R2 and am running both instances successfully. They are named:

  • COMPUTERNAME\SQLEXPRESS (2005)
  • COMPUTERNAME\INSTALL2 (2008)

I am trying to figure out how to specify the 2008 instance as the default server. I have tried:

  • Data Source=.\local; ...
  • Data Source=.; ...
  • Data Source=localhost; ...
  • Data Source=(local); ...
  • Data Source=127.0.0.1; ...

Each time there is a connection error stating...

Test connection failed because of an error in initializing provider. A 
network-related or instance-specific error occurred while establishing a 
connection to SQL Serve开发者_开发百科r. 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)

I have other developers who are working on the same project and only have SQL Server 2008 R2 installed and they are able to use Data Source=(local); ... to connect without any issues.

Is there a way for me to define my 2008 instance as the default or local instance?


You can use a Connection Alias. See How to: Create a Server Alias for Use by a Client


If you didn't choose "default instance" when you installed SQL Server, there's no way to change it after the fact without an uninstall/reinstall. You'll need to fully specify the instance name in your connection string.

Data Source=COMPUTERNAME\INSTALL2;


I know this is an older question and the suggestion of an alias was great as that is exactly what I tried to do.

However my server only two instances velociraptor\sql08R2 and velociraptor\sqlexpress and no default instance.

Connection strings with . or localhost or (local) would fail and if I changed the source code to my machine would break other devs local instances if checked in change.

So came up with better option to change the named pipe for the instance so that it would respond to the localhost options.

Changing the Pipe Name for the instance to "\.\pipe\sql\query"

You can find that setting by starting SQL Server Configuration Manager, and navigating to SQL Server Network Configuration > Protocols for (Instance Name) and right-clicking on Named Pipes and selecting Properties.

Can I specify the default SQL Server with two named instances installed?

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜