How to tell if smo.Server is on local machine

모든 응답

• 2009년 6월 1일 월요일 오전 9:52

   

   
  

  1
  Using SMO you can retrieve a list of available SQL Server installations on a network. You can specify arguments that control whether to search the network for SQL Server or to just search locally. The SmoApplication class exposes a static method named EnumAvailableSqlServers. When passed a Boolean value of true, the method will only search for and return all local servers. Passing a value of false to this method will cause it to search for all available servers on the network. The following code will get a list of SQL Server instances available on the network and return them to an ADO.NET DataTable:

  
  
  DataTable dtServers = SmoApplication.EnumAvailableSqlServers(true);
  foreach (DataRow row in dtServers.Rows)
  {
    string sqlServerName = row[“Server”].ToString();
    if (row[“Instance”] != null && row[“Instance”].ToString().Length > 0)
        sqlServerName += @”\” + row[“Instance”].ToString();
  }
  
  
  

  If True, only local instances are listed.

  If False, all instances are listed.

  
  
  -Sreekar
  • 응답
  • 인용

   
• 2009년 6월 1일 월요일 오전 9:59

   

   
  

  0
  Thanks Sreekar,
  
  I see a problem with your suggestion though. This may not work if the local machine does not have SQL Server Browser running. To my knowledge, the SmoApplication will only list sql servers where the SQL Server Browser is running. If the local SQL Server does not have it running, SmoApplication will not be able to see it, and the code will assume it is a remote sql server.
  • 응답
  • 인용

   
• 2009년 6월 1일 월요일 오후 2:15

  중재자

   

   
  

  0
  Hello,
  
  On the local machine, you may use another solution using the WMI and ManagedComputer.
  Please,could you have a look to this link ?
  for SQL Server 2008 : http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.wmi.managedcomputer.serverinstances.aspx
  for SQL Server 2005 : http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.wmi.managedcomputer.serverinstances(SQL.90).aspx
  
  Be careful, the dll you must reference is not the same according to the version of SMO dll installed on the machine executing the code.
  
  ManagedComputer is overloaded to try to get the SQL Server instances on the local or remote machine(s), but you may have security problems for remote computers  ( especially if you are not on a domain network or if you have not the persmissions to ask for WMI informations ).
  If i am doing an error, i think that Sreekar will be kind enough to correct me.
  
  For myself, i am using Sreekar's method ( but i am doing a double call to EnumAvailableSqlServers as it is possible to have some problems of timeout with SQL Server Express which are going to "sleep" quicklier than the other editions, maybe it is caused by the property AutoClose which is set to true on default in this case ).
  My method is surer on local machines but less reliable for remote computers ( you even can test whether the service of each instance is running thru the class Service ), exactly the opposite of EnumAvailableSqlServers
  
  For Sreekar, i am not sure but i think that EnumAvailableSqlServers without parameter is the same as with the parameter = true ( i may go wrong...)
  
  Have a nice day

  ---

  Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

  • 편집됨 Papy NormandModerator 2009년 6월 1일 월요일 오후 2:18 added the remark for Sreekar
  •  
  • 응답
  • 인용

   
• 2009년 6월 2일 화요일 오전 5:38

   

   
  

  0
  Thanks Papy,
  
  I tried that, but there was no obvious way to get the servers hostname from the ServerInstance.
  
  While playing around with this (on my dev and test machines), it dawned on me - the hostname is always in the SQL Server name (either the default instance - <HostName>, or a non-default instance - <HostName>\<Instance>. Once I realised this, it was quite simple. All I have to do was compare the hostname compoonent of the SQL Server name (Remove the instance bit if it is there) to System.Environment.MachineName (or IP address - just in case). I cant believe I was making this so difficult for myself.
  
  Thanks both of you for your help
  • 응답
  • 인용

   
• 2009년 6월 2일 화요일 오전 10:52

  중재자

   

   
  

  0
  Hello fweeee,
  
  I wrote too quickly my post related to the use of ManagedComputer :
  The constructor of ManagedComputer cannot manage more than one computer name :
  if you use
  ManagedComputer _mc = new ManagedComputer();
  you don't provide the name of the computer ==> you are looking on the local computer ( name : Environment.MachineName see the namespace System )
  if you use
  ManagedComputer _mc = new ManagedComputer("AComputer");
  you are creating a ManagedComputer for the computer named AComputer
  Be careful : you cannot use this syntax for the local computer ( with a message like WMI cannot access to the local computer )
  See :
  http://msdn.microsoft.com/en-us/library/ms194537.aspx
  It exists a 3nd syntax, in where you can provide the name of the windows user and its associated password ).
  See :
  http://msdn.microsoft.com/en-us/library/ms194585.aspx
  To avoid problems, i think the syntaxes with a computer name must be used when the computers are belonging to a network with a domain controller
  To get the name of the related computer , i think you should use _mc.Name
  http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.wmi.wmismoobject.name.aspx
  
  Don't hesitate to post again for more help or explanations
  
  Have a nice day

  ---

  Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
  • 응답
  • 인용

   
• 2011년 7월 3일 일요일 오후 4:19

  중재자

   

   
  

  0

  Hello,

  After 2 years ago, i am coming back on my last reply as i have done some tests about the ManagedComputer constructor with a parameter ( the name of the machine )

  ManagedComputer _mc = new ManagedComputer("(local") is working for the use of ManagedComputer versus the local machine

  ManagedComputer _mc = new ManagedComputer(".") is also working with shorter to code ( for the lazy developpers... )

  I wrote this post only to rectify an error in my previous post.

  But if your local machine is named "MyMachine", you have an error with the constructor ManagedComputer("MyMachine")

  Have a nice day

  ---

  Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
  • 응답
  • 인용