SmoApplication.EnumAvailableSqlServers() does not list default instance if sqlexpress is in same machine where SQLSERVER2008 installed

Question

I am using SMO to find available sqlservers of a network.But in one machine where i running

application it doesnot give Default instance name but for all other machines it gives the

named and default instance wil be showing.

Observe my Scenario.

Ex: Machine Name :rkwrk3-vm-sr (local machine from where i am running app in which

sqlserver2008 installed)

in this actually i have sql2008 as default instance and sqlexpress(2005) as named instance...

But it shows only one instance rkwrk3-vm-sr\sqlexpress and it doesnot ahows rkwrk3-vm-sr.

and some other machines Hr-2k3-tm(where sqlserver2005 installed)

for this it is showing Hr-2k3-tm and Hr-2k3-tm/sqlexpress

How can i fix this .Any Help wil Greately Appreciated.

Thanks in Advance.

score 1 · Answer 1 · answered Jul 15 '09 at 20:43

1

The instance discovery protocol (the UDP on 1434) is served by the SQL Browser service, which by default is disabled (in memory of a thing named Slammer...).

Make sure the service is enabled and started on all machines.

answered Jul 15 '09 at 20:43

Remus Rusanu

288,378
40
442
569

Port 1434 UDP has to be enabled in the firewall – Remus Rusanu Jul 16 '09 at 04:41
Firewall is disabled in my system where i am running the application.even incase of EnumAvailableServers(true) it shown only named instance.In my case that named instance is sql2005 express. – Cute Jul 16 '09 at 04:58
Default Insatnce is of type sqlsever2008 and named instance is of type sql2005. But it is missing to show Default instance.. – Cute Jul 16 '09 at 04:59
No, is not necessary to enable the firewall, but it is enabled, then the port 1434 UPD has to be open. – Remus Rusanu Jul 16 '09 at 06:30
Which SQL Browser is started, the one from the SQL 2005 installation or the one from 2008? Try cycle between them or even start both. – Remus Rusanu Jul 16 '09 at 06:31
All sqlservices are running...I have tested the same thing with the same setup on another machine in that machine it displays both named and default instance.But i am afraid the same setup is not displayed the default instance for my first machine – Cute Jul 16 '09 at 14:58

score 0 · Answer 2 · answered Jul 15 '09 at 15:40

You might have better luck using an alternate method to get the server list. Perhaps this method recommended by Microsoft that uses the System.Data.Sql.SqlDataSourceEnumerator.Instance

Enumerating Instances of SQL Server
http://msdn.microsoft.com/en-us/library/a6t1z9x2.aspx

Or this method:

Enumerate SQL Server Instances in C#, Using ODBC
http://www.codeproject.com/KB/database/SubmitSQLInfoEnumerator.aspx

SmoApplication.EnumAvailableSqlServers() does not list default instance if sqlexpress is in same machine where SQLSERVER2008 installed

2 Answers2

Linked