How to obtain all SQLServer services in the LAN

How to get all SQLServer services in the LAN Author: anonymous name I always want to get more detailed information about SQLServer in my applications. This idea was not implemented until SQLDMO (SQLDistributedManagementObjects) was recently used. SQLDMO provides very powerful functions, and we can use programs to implement any

How to get all SQL Server services in the LAN Author: anonymous name I always want to get more detailed information about SQL Server in my application. This idea was not implemented until SQLDMO (SQL Distributed Management Objects) was recently used. SQLDMO provides very powerful functions, and we can use programs to implement any

How to obtain all SQL Server services in the LAN

Author: anonymous name
I always wanted to get more detailed information about SQL Server in my application. This idea was not implemented until SQLDMO (SQL Distributed Management Objects) was recently used. SQLDMO provides very powerful functions. We can use programs to implement functions of any SQL Server. In this article, I will show you how to get all SQL Servers in the LAN, how to connect to them, and how to get all the databases on the server.

SQLDMO is similar to SQLDMO. dll, a dynamic Connection Library provided by SQL Server 2000. This dll is a COM Object. First, you must reference Microsoft SQLDMO Object Library (Version 8.0) from the Type Library ). delphi will automatically generate the SQLDMO_TLB.PAS file for you. The file contains interfaces of all COM objects.

Note that the introduced SQLDMO "TDatabase" and "TApplication" conflict with the other default class names that come with Delphi, therefore, you can change it to the _ TypeName format. Or another name. Here I will change it to T_Application, T_Database, and so on.

The next step is to introduce the unit file SQLDMO_TLB.PAS in our program. The application unit name is SqlServers.

The SERVER list contains all the SQL SERVER servers in the LAN. After selecting the SERVER, enter the user name and password and drop down the Database List. The program will list all the databases on the 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; // do not 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: TCloseAction );

Procedure FormShow (Sender: TObject );

Procedure BitBtn2Click (Sender: TObject );

Procedure CB_DataNamesDropDown (Sender: TObject );

Private

Server_Names: TStringList;

// Object set

PdmoObject: array of TdmoObject;

// Obtain all remote servers

Function GetAllServers (ServerList: TStringList): Boolean;

{Private declarations}

Public

{Public declarations}

End;

Var

FormServersList: TFormServersList;

Implementation

{$ R *. DFM}

{TForm1}

Function TFormServersList. GetAllServers (ServerList: TStringList): Boolean;

Var

SApp: _ Application;

SName: NameList;

IPos: integer;

Begin

Result: = True;

Try

SApp: = CoApplication _. Create; // you do not need to release the created object. delphi will release the object by itself.

SName: = sApp. ListAvailableSQLServers;

Except

Result: = False;

Exit;

End;

If sName. Count> 0 then // The reason why iPos starts from 1 is that the 0 position is null, that is''

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 ('the server list cannot be obtained, the client DLL library function 'may be missing, 'error message', 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]);

// Login security attribute, NT authentication

SQL _DMO.LoginSecure: = false;

// Set a connection timeout

SQL _DMO.LoginTimeout: = 3;

// Automatically re-log in. If the first attempt fails

SQL _DMO.AutoReconnect: = 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: TCloseAction );

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 ('check whether the user name or password is correct ', 'Connection failed', MB_ OK );

Exit;

End;

If not VerifyConnection (SQLDMOConn_ReconnectIfDead) then

Begin

ShowMessage ('error occurred when trying to connect to SQL SERVER 2000 '+ #10 #13 +

'Are you sure you have added the 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.