Board index » delphi » SQL Server Names & Databases

SQL Server Names & Databases

Does anyone know how I can retrieve the available SQL Server Names on a
Network (the same as the ODBC Administrator shows, when adding a new SQL
Server DSN) and add them to a TListBox and then when I click on an available
server in the list, it adds the available databases on that server in a
secondary listbox?

If anyone has a code example of this or something similar (it doesn't matter
how it is retrieved), it would be of great help.
I'm using D5 on NT 4.

Thanks for any help provided.

 

Re:SQL Server Names & Databases


There are 2 choices, use SQL-DMO, which is the programming model that
Enterprise Manager uses or look at the ODBC function SQLBrowseConnect, this
is used to enumerate SQL Servers.

-Euan

Quote
"NS" <gi...@nospam.netshift.com> wrote in message

news:93km4u$4g25@bornews.inprise.com...
Quote
> Does anyone know how I can retrieve the available SQL Server Names on a
> Network (the same as the ODBC Administrator shows, when adding a new SQL
> Server DSN) and add them to a TListBox and then when I click on an
available
> server in the list, it adds the available databases on that server in a
> secondary listbox?

> If anyone has a code example of this or something similar (it doesn't
matter
> how it is retrieved), it would be of great help.
> I'm using D5 on NT 4.

> Thanks for any help provided.

Re:SQL Server Names & Databases


It's also possible to load a list of servers registered on the workstation
from the system registry and after that you can log in to the master
database on the desired server and read sysdatabases.

As I had some bad experience with the list ODBC administrator generated
(only some of our servers showd up in the list) we had to register every
server. We also do not use standard ports - this might be the reason.

==========================
var
  rSQLServers: TRegistry;
  slSQLServers: TStringList;
begin
  slSQLServers:=TStringList.Create;
  try
    rSQLServers:=TRegistry.Create;
    try
      rSQLServers.Access:=KEY_READ;
      rSQLServers.RootKey:=HKEY_LOCAL_MACHINE;
      if
rSQLServers.OpenKey('SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo',
False) then
        rSQLServers.GetValueNames(slSQLServers)
      else
        slSQLServers.Add('No servers found!');

      rSQLServers.CloseKey;
    finally
      rSQLServers.Free;
    end;

    // Deleting the extra string
    // that does not represent a registered server
    // but is present in the list
    slSQLServers.Delete(slSQLServers.IndexOf('DSQUERY'));

    slSQLServers.Sort;

    // Populating the combo box with the SQL servers
    // registered on the current machine
    cbDBServerName.Items.Clear;
    cbDBServerName.Items.AddStrings(slSQLServers);
  finally
    slSQLServers.Free;
  end; // try finally
end;
==========================

Alex

Quote
"NS" <gi...@nospam.netshift.com> wrote in message

news:93km4u$4g25@bornews.inprise.com...
Quote
> Does anyone know how I can retrieve the available SQL Server Names on a
> Network (the same as the ODBC Administrator shows, when adding a new SQL
> Server DSN) and add them to a TListBox and then when I click on an
available
> server in the list, it adds the available databases on that server in a
> secondary listbox?

> If anyone has a code example of this or something similar (it doesn't
matter
> how it is retrieved), it would be of great help.
> I'm using D5 on NT 4.

> Thanks for any help provided.

Re:SQL Server Names & Databases


Euan,

Thanks for the pointers.  I am starting to learn DMO development but I'm
still a novice.  Do you have a Delphi code example of retrieving the
required info using the DMO that I could try?  Failing that, do you have an
example of the ODBC function you mentioned - this is something I have never
used.

Thanks for any help provided.

Quote
"Euan Garden" <euan.gar...@spicedham.usa.net> wrote in message

news:93l1ik$hpp4@bornews.inprise.com...
Quote
> There are 2 choices, use SQL-DMO, which is the programming model that
> Enterprise Manager uses or look at the ODBC function SQLBrowseConnect,
this
> is used to enumerate SQL Servers.

> -Euan

Re:SQL Server Names & Databases


Alexei,

Thanks for the tips on using the registry.  However, when I look at regedit
and the keys and values you mention below, all I find is the DSQUERY entry
and nothing else, even though we have three SQL Servers on our network and I
have apps and ODBC DSNs pointing to two of them.  When I look at the
"LastConnect" key however, I can see the two servers listed.  I was
wondering if I could reliably use this Key instead of the "ConnectTo" Key?
If I can, do you know how could I log onto the master database on the
desired server and read sysdatabases?

Thanks for any help provided.

Quote
"Alexei Malmyguine" <alex...@usa.net> wrote in message

news:3a5e27cb_1@dnews...
Quote
> It's also possible to load a list of servers registered on the workstation
> from the system registry and after that you can log in to the master
> database on the desired server and read sysdatabases.

> As I had some bad experience with the list ODBC administrator generated
> (only some of our servers showd up in the list) we had to register every
> server. We also do not use standard ports - this might be the reason.

> ==========================
> var
>   rSQLServers: TRegistry;
>   slSQLServers: TStringList;
> begin
>   slSQLServers:=TStringList.Create;
>   try
>     rSQLServers:=TRegistry.Create;
>     try
>       rSQLServers.Access:=KEY_READ;
>       rSQLServers.RootKey:=HKEY_LOCAL_MACHINE;
>       if
> rSQLServers.OpenKey('SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo',
> False) then
>         rSQLServers.GetValueNames(slSQLServers)
>       else
>         slSQLServers.Add('No servers found!');

>       rSQLServers.CloseKey;
>     finally
>       rSQLServers.Free;
>     end;

>     // Deleting the extra string
>     // that does not represent a registered server
>     // but is present in the list
>     slSQLServers.Delete(slSQLServers.IndexOf('DSQUERY'));

>     slSQLServers.Sort;

>     // Populating the combo box with the SQL servers
>     // registered on the current machine
>     cbDBServerName.Items.Clear;
>     cbDBServerName.Items.AddStrings(slSQLServers);
>   finally
>     slSQLServers.Free;
>   end; // try finally
> end;
> ==========================

> Alex

Other Threads