| Author |
Topic |
|
SQLServerExplorer
Starting Member
5 Posts |
 Posted - 2010-12-26 : 23:54:56
|
| Hi,I'm playing with writing some code which iterates through a server list and/or iterates through the databases in a specific server, generating stats or code along the way.There's no problem with programmatically getting a list of databases and then tables, given a specific server name. But, aside from using SQLDMO or SQLCMD -L, I can't seem to figure out how to get the list of servers inside a program.Based on responses to similar questions in other forums, one way to get a list of servers on the local machine is to use HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib\LastConnect. This seems to almost work, but there's an extra name in there - BVTSQL1. The command line sqlcmd -L agrees with the above registry key, except that it doesn't have that BVTSQL1 entry.If I provide a connection string in the program, something likesConnect := "Provider=SQLOLEDB;Data Source=" + sComputerName + ";Trusted_Connection=Yes;Initial Catalog=Master", and then try usingselect srvname from sys.sysservers, the program returns only the last server which was previously connected to.Is there any way - without using powershell or SQLDMO or the command line - to programmatically get a list of servers on the local machine? |
|
|
SQLServerExplorer
Starting Member
5 Posts |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2010-12-27 : 12:18:52
|
| This must be too easy to be what you want!Jimsp_helpserverFrom BooksOnLine:Reports information about a particular remote or replication server, or about all servers of both types. Provides the server name, the network name of the server, the replication status of the server, the identification number of the server, and the collation name. Also provides time-out values for connecting to, or queries against, linked servers. JimEveryday I learn something that somebody else already knew |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
|
|
SQLServerExplorer
Starting Member
5 Posts |
Posted - 2010-12-27 : 14:32:42
|
| ..What language are you programming in? Why not use DMO..Because, according to Microsoft, that interface will no longer be supported. Anyway, using the Registry, I'm able to get the server list.I just can't figure out what the first server - BVTSQL1 - is. It's obviously not really a server, but just the first entry in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib\LastConnect |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-12-27 : 15:43:36
|
| I meant SMO... |
|
|
|
SQLServerExplorer
Starting Member
5 Posts |
Posted - 2010-12-30 : 18:02:01
|
| SMO is a .net API, so we probably won't use it.Anyway, the registry method is not universal, unfortunately. The same registry keys that SQL Server generates in Vista, doesn't seem to exist in Windows 7. So this will have to be done by querying the 1433 port. |
|
|
|
SQLServerExplorer
Starting Member
5 Posts |
Posted - 2011-01-02 : 09:59:37
|
| I was using the wrong registry key, SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib\LastConnect, and getting partial results.The correct key, it seems, is SOFTWARE\Microsoft\Microsoft SQL Server, in the InstalledInstances entry.Dumb mistake on my part, it should have been obvious. |
|
|
|
|
List of local servers