allow user to select database Server

[tvssarma]

My application can use both Oracle and SQL Server as back end.
On the first run, it prompts the user to select a DB Server. Based on the selected DB Server, it will load the appropriate Data access components.

For example, I want to check if there is an Oracle (or SQL Server) Instance running -If it is running, I can use the instance to create my database etc. even if the instance is not running I should be able to retrieve the details of installed DBases on the Machine-that's what utilities like TOAD or TORA do.

My problem is, how do I enumerate available DB servers on a system. They can be either Oracle(any version) or SQL server, so that I can present a drop down list box of available DB servers for the user to select from.

I don't want to just enumerate the DSNs available on the machine. The problem is complicated since these databases can be installed on remote computers in a networked environment.

Any help is highly appreciated

[dglienna]

you can use a SELECT statement to list the DB's avalable for each one, individually

[Deepak Sakpal]

use sp_databases stored procedure.

it lists databases that reside in an instance of MS SQL Server. sp_databases returns the databases listed in the sysdatabases system table.

[tvssarma]

I am well aware of SP_Helpdb stored procedure and "SELECT * FROM tab" .
It's not the problem of listing the databases per se. It's the problem of listing the Database Servers or their instances.

[VIP3R]

ummm, I was looking at some API that might help and I ran across EnumServicesStatus which may come in handy. Do some research on it and it might be helpful. Another theoretical idea is to employ a parasitic method where you either learn how ODBC functions, or you use it to do the work for you.

[RobDog888]

'Add a reference to MS SQL DMO Object Library
Dim i As Integer
Dim oNames As SQLDMO.NameList
Dim oSQLApp As SQLDMO.Application
 
Set oSQLApp = New SQLDMO.Application
   
Set oNames = oSQLApp.ListAvailableSQLServers()
List1.Clear
For i = 1 To oNames.Count
    List1.AddItem oNames.Item(i)
Next

VB/Outlook Guru!

[dglienna]

he wants to see if a specific db server is already running on the machine. Oracle or SQL.
that does SQL

[RobDog888]

Originally posted by tvssarma
...It's the problem of listing the Database Servers or their instances.

My code will solve half the problem, finding all SQL servers on the
network. All thats left is to find all Oracle servers on the network.

[tvssarma]

Originally posted by RobDog888
My code will solve half the problem, finding all SQL servers on the
network. All thats left is to find all Oracle servers on the network.

Hi Robdog888,
Does it work on WinME ? Because When I tried the code, it did not list the SQL server instance running on my system.(SQL Server 7.0). I have MDAC 2.7 installed. does it work with this?
where do I get documentation for SQL DMO. because what I got was for C/C++

[szlamany]

From the MS Books Online

ListAvailableSQLServers Method
The ListAvailableSQLServers method returns a NameList object that enumerates network-visible instances of Microsoft® SQL Server™ 2000.

Applies ToApplication Object


Syntax
object.ListAvailableSQLServers( ) as NameList

Parts
object

Expression that evaluates to an object in the Applies To list

Prototype (C/C++)
HRESULT ListAvailableSQLServers(
LPSQLDMONAMELIST* ppServerNames);

Returns
A NameList object that enumerates instances of SQL Server.

Remarks
Nondefault instances of SQL Server are displayed in the form of SERVERNAME/INSTANCENAME.

The ListAvailableSQLServers method is supported only for servers and workstations running Microsoft Windows NT® 4.0 and Microsoft Windows 2000.



Note ListAvailableSQLServers maps to the ODBC SQLBrowseConnect function, which does not support connection pooling. Therefore, an application that enables connection pooling might encounter the error "Microsoft SQL-DMO (0x800A000E) [SQL-DMO]Not enough storage is available to complete this operation." when calling ListAvailableSQLServers.

ListAvailableSQLServers maps to the ODBC function SQLBrowseConnect.

If you don't have MS Books On-line, try searching the MSDN site for information on either of these.

I would imagine that ODBC has a way to find ORACLE servers as well.

[RobDog888]

If you install the SQL Client Tools on your development system,
you will then have the MS SQL DMO Object Library in you list for
adding references to your vb project. When you create your
package it will pickup the dependancies for SQLDMO also.

[tvssarma]

I have SQL-DMO installed and added reference etc. I checked the documentation. but the problem is when I tested it on my WInME machine with SQLserver 7.0, it did not return the name of the server.

[szlamany]

Originally posted by tvssarma
I have SQL-DMO installed and added reference etc. I checked the documentation. but the problem is when I tested it on my WInME machine with SQLserver 7.0, it did not return the name of the server.

From my post above (directly cut/paste put of the MS BOL)...

The ListAvailableSQLServers method is supported only for servers and workstations running Microsoft Windows NT® 4.0 and Microsoft Windows 2000.

[ducky]

For Oracle, you'll probably just have to parse the users TNSNAMES.ORA file. That's all TOAD does to display its list of databases to connect to.

[tvssarma]

Originally posted by szlamany
From my post above (directly cut/paste put of the MS BOL)...

You are absolutely correct. It was an oversight. I apologize