How to get all SQL Server servers in a local area network
Document Reference Source: Delphi Depth Exploration
I have always wanted to get more detailed information about SQL Server in my application. Until recently used SQLDMO (SQL Distributed Management Objects) this idea. SQLDMO provides a very powerful feature, which can almost use programs to implement any SQL Server ownership. In this article I will show you how to get all SQL Servers servers in the LAN, how to connect, how to get all the databases in the server.
SQLDMO has dynamic connection library SQLDMo.dll from SQL Server 2000. This DLL itself is a COM object. First, you must reference Microsoft SqldMo Object Library (Version 8.0) from the type library. Delphi automatically generates SQLDMO_TLB.PAS files, including all COM object interfaces.
Here we need to note that due to the introduced SqldMo "TDatabase" and "Tapplication" and several other default class names conflict with Delphi, they can modify the form of _typename. Or other names, I am changed here into T_Application, T_Database, etc.
Let's take the next step to introduce unit files SQLDMO_TLB.PAS in our program. Application unit name is SQLServers
The program running interface is as follows:
The server list is all the SQL Server servers in the LAN. After selecting the server, enter the username and password, drop down the Database list, the program lists all the databases in this server.
The program source code is as follows:
Unit SQLServers;
Interface
Uses
Windows, Messages, Sysutils, Classes, Graphics, Controls, Forms, Dialogs,
Stdctrls, Buttons, Comctrls, SqldMo_TLB; // Note Don't forget to introduce this file
Type
TDMOOBJECT = Record
SQL_DMO: _SQLSERVER;
LCONNECTED: BOOLEAN;
END;
Type
TFORMSERVERSLIST = Class (TFORM)
Label1: TLABEL;
Label2: TLABEL;
CB_SERVERNAMES: TcomboboX;
CB_DATANAMES: Tcombobox;
Label3: TLABEL;
Label4: TLABEL;
Ed_login: tedit;
Ed_pwd: tedit;
Bitbtn1: tbitbtn;
Bitbtn2: tbitbtn;
Procedure formcreate (Sender: TOBJECT);
Procedure formcloseQuery (Sender: Tobject; Var CANClose: Boolean);
Procedure formclose (Sender: Tobject; VAR Action: Tclosection);
Procedure FormShow (Sender: TOBJECT);
Procedure Bitbtn2Click (Sender: TOBJECT);
Procedure cb_datanamesdropdown (sender: TOBJECT);
Private
Server_names: tstringlist;
// Object Collection
PDMOOBJECT: ARRAY OF TDMOOBJECT;
/ / Get all remote servers
Function Getall Servers (ServerList: tstringlist): boolean;
{Private Declarations}
public
{Public declarations}
END;
VAR
FormServerslist: tFormServerslist;
IMPLEMENTATION
{$ R * .dfm}
{TFORM1}
Function TFormServersList.Getall Servers (ServerList: tstringlist): boolean;
VAR
Sapp: _Application;
Sname: Namelist;
IPOS: Integer;
Begin
RESULT: = TRUE;
Try
Sapp: = coApplication_.create; // The object created is not released, Delphi will release itself
Sname: = sapp.listavailablesqlservers;
Except
Result: = FALSE;
EXIT;
END;
If SNAME.COUNT> 0 THEN / IPOS starts from 1, because 0 position is empty value, ''
For ipos: = 1 to Sname.count - 1 DO
Begin
CB_ServerNames.Items.Add (Sname.Item (IPOS));
ServerList.Add (Sname.Item (IPOS));
END;
END;
Procedure TFormServersList.formCreate (Sender: TOBJECT);
VAR
LCV: Integer;
Begin
Server_names: = TSTRINGLIST.CREATE;
IF not getallservers (Server_names) THEN
Begin
Application.MessageBox ('Unable to get a list of server, may miss the client DLL library function', 'error prompt', MB_OK);
EXIT;
END;
For LCV: = 0 TO Server_Names.count - 1 DO
Begin
SETLENGTH (PDMOOBJECT, LCV 1);
With pdmoobject [lcv] do
Begin
SQL_DMO: = COSQLSERVER.CREATE;
SQL_DMO.NAME: = Trim (Server_Names [LCV]);
// Log in security attribute, NT authentication
SQL_DMO.LOGINSECURE: = false;
// Set a connection timeout
SQL_DMO.LOGINTIMEOUT: = 3;
// Automatically re-landed, if the first failed
SQL_DMO.AUTORECONNECONNECT: = True;
SQL_DMO.ApplicationName: = Server_Names [LCV];
LCONNECTED: = false;
END;
END;
END;
Procedure TFormServersList.FormCloseQuery (Sender: Tobject; Var CANCLOSE: Boolean);
Begin
Server_names.free;
END;
Procedure TFormServersList.FormClose (Sender: Tobject; VAR Action: Tclosection);
Begin
Action: = CAFREE;
END;
Procedure TFormServersList.FormShow (Sender: TOBJECT);
Begin
If CB_SERVERNAMES.ITEMS.COUNT> 0 THEN / / list all server names CB_SERVERNAMES.TEXT: = CB_SERVERNAMES.ITEMS.STRINGS [0];
END;
Procedure TFormServersList.bitbtn2Click (Sender: TOBJECT);
Begin
CLOSE;
END;
Procedure tFormServerslist.cb_datanamesdropdown (sender: TOBJECT);
VAR
ICOUNT, Server_B: Integer;
Begin
CB_Datanames.clear;
Screen.cursor: = CRHOURGLASS;
Server_b: = CB_ServerNames.Items.indexof (CB_ServerNames.Text);
With pdmoobject [server_b]. SQL_DMO DO
Begin
IF not pdmoobject [server_b] .lconnected then
Try
Connect (name, trim (ed_login.text), TRIM (Ed_Pwd.Text));
Except
Screen.cursor: = CRDEFAULT;
Application.MessageBox ('Please check if the username or password is correct', 'connection failed', MB_OK);
EXIT;
END;
If not verifyconnection (SqldMoconn_ReconnectifDead) THEN
Begin
ShowMessage ('in tried to connect to SQL Server 2000 error' # 10 # 13
'Confirmation is adding a dynamic connection library SQLDMo.dll');
EXIT;
END ELSE
PDMOOBJECT [Server_B] .lconnected: = true;
Databases.Refresh (True);
For iCount: = 1 to Databases.count DO
CB_Datanames.Items.Add (DatabaseS.Item (iCount, Null) .name;
END;
Screen.cursor: = CRDEFAULT;
end
End.
