How can I detect available SQL server instances in the neighboring network?

How can I detect available SQL server instances in the neighboring network?

• DRoden

  I also already voted. I really need this. Seems like deployment of application that relies on SQL Server will never be easy if such feature is not working well.
  
  

• VladR

  Hi Lyle,

  After you have navigated to the feedback item at the address specified by the link, you need to sign in. If you have not signed in at this site before, you may be required to register. But once you are finally in, there should be a "Vote Now" link in the Rating box. You can specify a rating between 1 and 5 based on the importance of the problem as you see it.

  Regards,

  David.

  
  

• Alex2200

  If you are using SQL Server 2005 then it has SMO library that you could use in your code to get a list of the servers. Earlier versions have DMO library (which is a COM) and you could use it to get a list
  
  

• John Fly

  Hi Jamie,

  I am frustrated by SqlDataSourceEnumerator as well.

  I have created a feedback item at :-

  http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx FeedbackID=146645

  If you could vote for it, that would be great.

  Thanks,

  David Riddiford

  
  

• sully111

  Hi Jamie,

  Voted for your bug as requested.

  Cheers,

  David.

  
  

• JocularJoe

  Hello,

  You actually need to call EnumAvailableSQLServers not just once, but multiple times.

  I actually saw this recommended by microsoft, however I can't find the link, sorry.

  The reason for this is that some instances out there may timeout before they're able to respond to the broadcast on UDP 1434. So, if you create a list/array etc and store the results of EnumAvailableSQLServers, then call EnumAvailableSQLServers again and append any newly-found instances from the second call - you'll find that there will be more instances discovered.

  You can see this in action when you execute osql -L from the command-line (which again just sends a boradcast on udp 1434)

  If you want a little more control, you can just create a socket object in c# and broadcast on udp 1434 with a data value of 0x2 to receieve the announcements from listening instances. Note that if an instance is hidden, you'll not receieve any response...

  Hope this helps a little.

  Cheers

  Rob

  
  

• SynergyNT

  for example, if I log in using admisnistrator account of local machine, and I use my code calling SMO, I get only the instance of SQLExpress ( Machine1\SQLExpress). But when I use SQL Management Studio Express to browse, I got Machine1 (SQL Server Developer) and Machine1\SQLExpress. Can anyone explain this
  
  

• RussP

  Is there any rhyme or reason to why it shows up I mean, are you using the same account each time when you are on different machines.
  
  

• dgolds

  David,
  
  Ditto. Added a bug to the SQL Server feedback centre as well.
  
  http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx FeedbackID=146323
  
  If you might vote for mine that would be good a well. Suspect this is a duplicated item. I picked the SQL Server team to log problem.
  
  Regards
  
  
  

• Peter D.252325

  II-Sung,

  I'm very disappointed in functions available to software developers in this area. If neither the Microsoft.SqlServer.Management.Smo.SmoApplication.EnumAvailableSqlServers or the System.Data.Sql.SqlDataSourceEnumerator functions can provide a consistent list of information then shouldn't the online documenation (MSDN/SQL Server) cross reference each other and suggest code might help us poor developers get things working.

  This makes us (and Microsoft) look very unprofessional if we want to deploy out of the box software/database solutions that use Microsoft SQL Server.

  If we have to install SQL Server 2005 Express on a PC, then obviously these functions are no use to us when trying to determine if an existing service is available. If we want to install a database onto a corporate server, giving the use an incomplete list to pick from is also hit and miss (not very friendly for our technically limited users/paying customers). We have to deliver software that uses databases because that is 95% of the software our company provides, I don't care if I have to write 5 pages of code to get an accurate list of servers and if the user has to wait a few minutes to get a list that's much smarter than the results from the functions in .net 2 and SMO 2005.

  Where do we go from here

  
  
  

• GeneralCody

  Hi David,

  Thanks for feedback item, how do I vote yes for it on the link It is a serious problem indeed.

  
  

• Lino!

  Hi

  If you're using the .Net Framework 2.0, then you can use the SqlDataSourceEnumerator.GetDataSources() method (http://msdn2.microsoft.com/en-us/library/system.data.sql.sqldatasourceenumerator.getdatasources.aspx).  Please keep in mind that since the underlying mechanism relies upon a UDP broadcast, the results are not deterministic.

  Thanks,
  Il-Sung.

  
  
  

• comspy

  hello

  i search in the same forrest... :-) please try with open the local windows xp firewall. (port tcp 1433, 1434 and udp port 1434). possbile that this will help... :-)

  regards

  mici

  
  

• neurobion

  I've tried SMO but still sometimes it doesn't read some available instances. For example, in my machine I have SQL Server Developer and SQL Server Express, SMO gives me just the SQL Express instance. In another case, when I run in some machines with only SQL Server Express, sometimes, it reads nothing. I really don't understand. Could you please explain why

  Here is my code:-

  cmbServerName.Items.Clear()

  Dim dtSQLServers As DataTable = SmoApplication.EnumAvailableSqlServers(False)

  Dim I As Integer

  For I = 0 To dtSQLServers.Rows.Count - 1

  Dim ServerName As String = dtSQLServers.Rows(I)("Server").ToString()

  If (dtSQLServers.Rows(I)("Instance") IsNot Nothing And dtSQLServers.Rows(I)("Instance").ToString().Length > 0) Then

  ServerName = ServerName + "\" + dtSQLServers.Rows(I)("Instance").ToString()

  cmbServerName.Items.Add(ServerName)

  End If

  Next I

  
  
  
• How can I detect available SQL server instances in the neighboring network?