listavailablesqlservers returns only default instances - please help!

Hiya,
I'm trying to write an application in which I call listavailablesqlservers()
(using
SQLDMO). My intention is to get a list of all available sql server instances
available on the network (including the machine on which the app is
running). However, listavailablesqlservers() seems to return only the
default instances and ignores all other instances.
Anyone else encounter this problem or have a work around?
I'm developing using VB.NET on a Windows XP Professional machine running
both SQL Server Developer edition (my default instance) and an MSDE named
instance.
Any help would be greatly appreciated as I've unsuccessfully trawled the
Internet looking for an answer.
Thanks in advance.
Kas.Kas,
Does this help?
http://msdn.microsoft.com/library/default.asp
?url=/library/en-us/sqldmo/dmoref_p_i_95gp.asp
Russell Fields
"rearwindow" <summerof_89@.hotmail.com> wrote in message
news:bsarm9$as3$1$8302bc10@.news.demon.co.uk...
> Hiya,
> I'm trying to write an application in which I call
listavailablesqlservers()
> (using
> SQLDMO). My intention is to get a list of all available sql server
instances
> available on the network (including the machine on which the app is
> running). However, listavailablesqlservers() seems to return only the
> default instances and ignores all other instances.
> Anyone else encounter this problem or have a work around?
> I'm developing using VB.NET on a Windows XP Professional machine running
> both SQL Server Developer edition (my default instance) and an MSDE named
> instance.
> Any help would be greatly appreciated as I've unsuccessfully trawled the
> Internet looking for an answer.
> Thanks in advance.
> Kas.
>

ListAvailableSQLServers does not show local instances

Hi all

I am using SQL DMO method ListAvailableSQLServers to get the list of all the SQL server available to the local machine.

For some reason, I get all the servers except the instances installed in the local machine. - The are all started.

Any help?

thanks.

I have seen some issues with latency (some servers may not respond fast enough). Also the server instance may be marked hidden.

Start SQL Computer Manager, right click on the Protocols node of the instance and select Properties. Then see if HideInstance is set to No (if not switch to "No" and restart server).

Also try SQLCMD -L

If you see your local instance then it may be a bug in DMO.

|||Thanks Michiel.

I forgot to say that I am working with SQL 2000. I can't find the SQL Computer Manager. Also i try isql -L and I get only the default instance in my machine and nothing else, i.e., not the other instance and not any of the network SQL servers.

In the SQL Server Service Manager I can see all the available servers.
any help ?|||you can use the registry for search local instances

Example:

//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 (cmbServers.FindStringExact(name) == -1)
cmbServers.Items.Add(name);
}

the complete Code (smo) can you find at http://www.sqldbatips.com/showarticle.asp?ID=45

it works with dmo too

List of SQL Servers in a Domain

Is there a sure way of finding out all the SQL Server instances in a
given domain? I have read few post but have not seen anything that
will give accurate result. I am in the need of it, can anyone please
help me. Any help in this reagrd will be greatly be appreciated.
Thanksshub wrote:
> Is there a sure way of finding out all the SQL Server instances in a
> given domain? I have read few post but have not seen anything that
> will give accurate result. I am in the need of it, can anyone please
> help me. Any help in this reagrd will be greatly be appreciated.
> Thanks
>
HOPE THIS HELPS:
http://ryanfarley.com/blog/archive/2003/11/11/218.aspx|||On Oct 30, 5:31 am, shub <shubt...@.gmail.com> wrote:
> Is there a sure way of finding out all the SQL Server instances in a
> given domain? I have read few post but have not seen anything that
> will give accurate result. I am in the need of it, can anyone please
> help me. Any help in this reagrd will be greatly be appreciated.
> Thanks
Search for a utility called SQLPING

List of SQL Server 7.0/2000 instances running

How to get the list of instance of SQL Server 7.0/2000 running on the local machine inside my domain...
I need to prepare the list of all sql instances.. pls help if possible to find details using sql query.I'd start with:EXECUTE master.dbo.xp_cmdshell 'osql -L'You might have to refine it a bit, but it is a place to start.

-PatP

List available SQL instances in a Server

Hi, iam using this script to connect to the default instance of a SQL Server
(7 or 2000)
strcomputer = wscript.arguments(0)
Set oServer=CreateObject("SQLDMO.Sqlserver")
oserver.loginsecure = true
oServer.Connect strComputer
The problem is:
When i try to connect to a server that has many instances
(MSSQLSERVER$XXXXXX) i cant connect. This scripts only works with the defaul
t
mssqlserver. The question is how can connect to a server and list the
available instances instead of hardcoding in code (Like strcomputer &
"\Instance") using vbscript or other shell tool. (The discover tool like osq
l
-L doesn't work, because it cant detect server over lot of networks)
Thanks all.Use the ListAvailableServers Method of the application object.
INF: How to Enumerate Available SQL Servers Using SQLDMO
http://support.microsoft.com/defaul...b;en-us;Q287737
AMB
"Tinchos" wrote:

> Hi, iam using this script to connect to the default instance of a SQL Serv
er
> (7 or 2000)
> strcomputer = wscript.arguments(0)
> Set oServer=CreateObject("SQLDMO.Sqlserver")
> oserver.loginsecure = true
> oServer.Connect strComputer
> The problem is:
> When i try to connect to a server that has many instances
> (MSSQLSERVER$XXXXXX) i cant connect. This scripts only works with the defa
ult
> mssqlserver. The question is how can connect to a server and list the
> available instances instead of hardcoding in code (Like strcomputer &
> "\Instance") using vbscript or other shell tool. (The discover tool like o
sql
> -L doesn't work, because it cant detect server over lot of networks)
> Thanks all.|||Thanks Alejandro. But it doesn't work neither. It's the same that use the
osql -L (only works on local network)
I need to find a method to connect to a server and list the available
instances with dmo.
Thanks !!
"Alejandro Mesa" wrote:
> Use the ListAvailableServers Method of the application object.
> INF: How to Enumerate Available SQL Servers Using SQLDMO
> http://support.microsoft.com/defaul...b;en-us;Q287737
>
> AMB
> "Tinchos" wrote:
>|||See if querying master.dbo.sysservers or executing sp_helpserver helps.
AMB
"Tinchos" wrote:
> Thanks Alejandro. But it doesn't work neither. It's the same that use the
> osql -L (only works on local network)
> I need to find a method to connect to a server and list the available
> instances with dmo.
> Thanks !!
> "Alejandro Mesa" wrote:
>|||Nop, i have a about 50 sql server over the network.
New sql servers are installed with instances, like this:
SERVER\UNLAMXEX
SERVER\UNLAMXFO
SERVER\UNLAMXPR
I need to specify the instance to connect with isql o other. I need some
scripting tool to detects automatically the instances.
Thanks.
Martin
"Alejandro Mesa" wrote:
> See if querying master.dbo.sysservers or executing sp_helpserver helps.
>
> AMB
> "Tinchos" wrote:
>

List All Instances of SQL Server

Hi all,
I am trying to generate a list of all available SQL Servers (named instances
and all) on a network. I have seen over and over again to use SQLDMO or isq
l
-L.
The problem that I am having is that these methods only seem to want to
return one instance from each computer.
ex. Computer "Main" has
Main
Main\Instance1
Main\Instance2
These methods are only returning "Main" in the list and not the rest of the
named instances. I have tried everything I can think of, including making
sure the protocols "named pipes" and "TCP/IP" are activated for each
instance. No matter what I have tried these Names won't return. I have
resorted to reading the registry to get get the instances for the local
computer from the "InstalledInstances" key.
This method is fine for the local computer but won't work for network
computers.
Do any of you have any suggestions for returning a complete list of all
available servers?
Is there something I'm doing wrong or missing?
Also I would like to return a list of local servers when the network cable
is unplugged. Do you have a suggestion for how to solve this problem?
Thanks for any help,
KenKen,
I was trying to do the same thing. Only thing I found that returned my
Instances was the following.
Only thing is not sure how well this will work in a network environment
since this is reading the registry. If you figure how to do it on a network
or a different way let me know your solution.
RegistryKey objInstances = Registry.LocalMachine;
objInstances = objInstances.OpenSubKey(@."SOFTWARE\Microsoft\Microsoft SQL
Server\Instance Names\SQL", true);
foreach (string Keyname in objInstances.GetValueNames())
{
tvTableInfo.Nodes.Add(Keyname);
}
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:E4BE412C-A98C-4522-B902-5AC1398F2F72@.microsoft.com...
> Hi all,
> I am trying to generate a list of all available SQL Servers (named
> instances
> and all) on a network. I have seen over and over again to use SQLDMO or
> isql
> -L.
> The problem that I am having is that these methods only seem to want to
> return one instance from each computer.
> ex. Computer "Main" has
> Main
> Main\Instance1
> Main\Instance2
> These methods are only returning "Main" in the list and not the rest of
> the
> named instances. I have tried everything I can think of, including making
> sure the protocols "named pipes" and "TCP/IP" are activated for each
> instance. No matter what I have tried these Names won't return. I have
> resorted to reading the registry to get get the instances for the local
> computer from the "InstalledInstances" key.
> This method is fine for the local computer but won't work for network
> computers.
> Do any of you have any suggestions for returning a complete list of all
> available servers?
> Is there something I'm doing wrong or missing?
> Also I would like to return a list of local servers when the network cable
> is unplugged. Do you have a suggestion for how to solve this problem?
> Thanks for any help,
> Ken|||Thanks, but that is essentially what I accomplished by reading the
"NamedInstances" key. That type of situation works well for the local
computer but like you said, it won't work for network machines.
Thanks for the input though.
"JP" wrote:

> Ken,
> I was trying to do the same thing. Only thing I found that returned my
> Instances was the following.
> Only thing is not sure how well this will work in a network environment
> since this is reading the registry. If you figure how to do it on a networ
k
> or a different way let me know your solution.
> RegistryKey objInstances = Registry.LocalMachine;
> objInstances = objInstances.OpenSubKey(@."SOFTWARE\Microsoft\Microsoft SQL
> Server\Instance Names\SQL", true);
> foreach (string Keyname in objInstances.GetValueNames())
> {
> tvTableInfo.Nodes.Add(Keyname);
> }
>
>
> "Ken" <Ken@.discussions.microsoft.com> wrote in message
> news:E4BE412C-A98C-4522-B902-5AC1398F2F72@.microsoft.com...
>
>|||I am using sql-dmo to get list of instances. I have the same problem. But
i figured out that when i disable my local firewall application sql-dmo
returns all SQL Server instances. So try to temporarily turn off your
firewalls.
On Fri, 19 Aug 2005 02:22:04 +0300, Ken <Ken@.discussions.microsoft.com>
wrote:

> Hi all,
> I am trying to generate a list of all available SQL Servers (named
> instances
> and all) on a network. I have seen over and over again to use SQLDMO or
> isql
> -L.
> The problem that I am having is that these methods only seem to want to
> return one instance from each computer.
> ex. Computer "Main" has
> Main
> Main\Instance1
> Main\Instance2
> These methods are only returning "Main" in the list and not the rest of
> the
> named instances. I have tried everything I can think of, including
> making
> sure the protocols "named pipes" and "TCP/IP" are activated for each
> instance. No matter what I have tried these Names won't return. I have
> resorted to reading the registry to get get the instances for the local
> computer from the "InstalledInstances" key.
> This method is fine for the local computer but won't work for network
> computers.
> Do any of you have any suggestions for returning a complete list of all
> available servers?
> Is there something I'm doing wrong or missing?
> Also I would like to return a list of local servers when the network
> cable
> is unplugged. Do you have a suggestion for how to solve this problem?
> Thanks for any help,
> Ken|||That was it.
I was using a software firewall and had disabled that to see if it was the
problem, and I had assumed that the windows firewall was disabled(as I had
previously disabled it). Once I disabled the windows firewall all instances
started showing up.
Thanks for the response,
Ken
"Igor Solodovnikov" wrote:

> I am using sql-dmo to get list of instances. I have the same problem. But
> i figured out that when i disable my local firewall application sql-dmo
> returns all SQL Server instances. So try to temporarily turn off your
> firewalls.
> On Fri, 19 Aug 2005 02:22:04 +0300, Ken <Ken@.discussions.microsoft.com>
> wrote:
>
>