| Author |
Topic |
|
dbaman
Starting Member
46 Posts |
 Posted - 2007-10-04 : 08:34:37
|
| Hello All,I am working for a company with approx. 5000 computers(Mix of Win2000+WinXP-PRO). I am asked to write some script or find some kind of tool or mehanism to scan the network to see how many PCs are running SQLServer ( Any version -- may be Desktop/Standard Edition). Is there any such way/script/tool anyone knows here . Please share your experience.Thanks A lot.R |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-10-04 : 08:42:42
|
| http://www.sqlteam.com/article/finding-sql-servers-running-on-a-network_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
 |
|
|
dbaman
Starting Member
46 Posts |
Posted - 2007-10-04 : 08:50:55
|
| Thanks a lot Spirit1.R |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-10-04 : 09:50:18
|
that article is a little old. the recommendation to use DMO is stale as DMO is deprecated in 2005.in managed code, you can use this: System.Data.Sql.SqlDataSourceEnumerator.Instance.GetDataSources()
elsasoft.org |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-10-04 : 10:34:13
|
i wish you would have told me about that method sooner...  _______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-10-04 : 10:39:59
|
eh, it's not that great. I think it depends on the sqlbrowser service running on each server.
elsasoft.org |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-10-04 : 10:44:13
|
i used this for populating my servers in my tablediff.exe gui:List<string> servers = new List<string>();try{ // SMO Enum Servers DataTable dt = SmoApplication.EnumAvailableSqlServers(false); if (dt.Rows.Count > 0) { // Load server names into combo box foreach (DataRow dr in dt.Rows) { //only add if it doesn't exist if (!servers.Contains(dr [ "Name" ].ToString().ToUpper())) servers.Add(dr [ "Name" ].ToString().ToUpper()); } } //Registry for local RegistryKey rk = Registry.LocalMachine.OpenSubKey(@"SOFTWARE\Microsoft\Microsoft SQL Server"); String[] instances = (String[])rk.GetValue("InstalledInstances"); if (instances.Length > 0) { foreach (string element in instances) { string name = ""; //only add if it doesn't exist if (element == "MSSQLSERVER") name = System.Environment.MachineName; else name = System.Environment.MachineName + @"\" + element; if (!servers.Contains(name.ToUpper())) servers.Add(name.ToUpper()); } } // Registered Servers RegisteredServer[] rsvrs = SmoApplication.SqlServerRegistrations.EnumRegisteredServers(); foreach (RegisteredServer rs in rsvrs) { string name = ""; IPAddress ip; // only replace dots if it's not IP address if (!IPAddress.TryParse(rs.ServerInstance, out ip)) name = rs.ServerInstance.Replace(".", System.Environment.MachineName) .Replace("(local)", System.Environment.MachineName) .Replace("localhost", System.Environment.MachineName); else name = rs.ServerInstance; //only add if it doesn't exist if (!servers.Contains(name.ToUpper()) && name.Length > 0) servers.Add(name.ToUpper()); } return servers;} catch (Exception ex){ // handle exception}return null;_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
Haywood
Posting Yak Master
221 Posts |
Posted - 2007-10-04 : 11:37:10
|
quote:
Originally posted by jezemine
that article is a little old. the recommendation to use DMO is stale as DMO is deprecated in 2005.in managed code, you can use this: System.Data.Sql.SqlDataSourceEnumerator.Instance.GetDataSources()
elsasoft.org
FWIW, DMO is not deprecated in 2005. Nor is it deprecated yet in 2008 (as of the July CTP)....If you have SQL2005 you can also use the "sqlcmd /Lc" command from a command shell. The c switch indicates 'clean' output... I use this to populate a table of servers on the network and it works fine - with the exception that it reads from AD and will list servers no longer on the network if they have not been removed from Active Directory.... |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-10-04 : 19:47:00
|
well, I know when DMO was released with 2005, it was released with deprecated status. perhaps customer feedback has been strong enough to keep it going though.
elsasoft.org |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-10-04 : 19:51:39
|
quote:
Originally posted by spirit1
i used this for populating my servers in my tablediff.exe gui:List<string> servers = new List<string>();try{ // SMO Enum Servers DataTable dt = SmoApplication.EnumAvailableSqlServers(false);
if you look at SMO in reflector, you'll find that EnumAvailableSqlServers() is just calling the method I posted.
elsasoft.org |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-10-05 : 05:35:59
|
| well.. i didn't look _______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
|
Scanning Network to See running SQLServer