Solved

Obtain SQL Server Status of all Servers on my LAN when app is run as a service

Posted on 2003-11-02
5
157 Views
Last Modified: 2010-04-05
Hi,

I have been able to obtain a list of all SQL Servers on my LAN using either SQLDMO or NetServerEnum and when the application is run as an app, I am able to return their status using SQLDMO. My problem is when I run the app as a service, I can only return the status of the local SQL Server and not the ones on my LAN... I know it must have something to do with the account used for the service but have been unable to sort it out... sample code would be appreciated..
0
Comment
Question by:cardax
  • 2
5 Comments
 

Expert Comment

by:huiyue
ID: 9669083
First,You must install the component :SQLDMO.DLL
it may be in the folder:Program Files\Microsoft SQL Server\80\Tools\Binn
then ,Try the following codes:

function SQLServerList(var ServerList:Tstrings):integer;
var
  SQLServer:Variant;
  SvrList:Variant;
  i,nServers:integer;
  sRetValue:String;
begin
 SQLServer := CreateOleObject('SQLDMO.Application');
 SvrList:= SQLServer.ListAvailableSQLServers;
 nServers:=SvrList.Count;
 serverlist.Clear;
 for i := 1 to nservers do
     serverlist.Add(svrlist.Item(i));
 SQLServer:=NULL;
 svrList:=NULL;
 result:=nServers;
end;

Good Luck!
yuehua
0
 

Author Comment

by:cardax
ID: 9676249
Thanks but I already have this working.. I have added the Status code ... if run as an application it works fine, but not as a service.

function SQLServerList(var ServerList:Tstrings):integer;
var
  SQLServer:Variant;
  SQLServerStatus:Variant;
  SvrList:Variant;
  i,nServers:integer;
  sRetValue,sTmpStr:String;
begin
 SQLServer := CreateOleObject('SQLDMO.Application');
 SQLServerStatus := CreateOleObject('SQLDMO.SQLServer');    //added to obtain SQL Server Status
 SvrList:= SQLServer.ListAvailableSQLServers;
 nServers:=SvrList.Count;
 serverlist.Clear;
 for i := 1 to nservers do begin
    SQLServerStatus.Name := svrlist.Item(i);
    case SQLServerStatus.Status of
      SQLDMOSvc_Continuing :  sTmpStr := 'SQL Server on ' + svrlist.Item(i) + ' state is in transition from paused to running.';
      SQLDMOSvc_Paused     :  sTmpStr := 'SQL Server on ' + svrlist.Item(i) + ' is paused and cannot accept transactions.';
      SQLDMOSvc_Pausing    :  sTmpStr := 'SQL Server on ' + svrlist.Item(i) + ' state is in transition from running to paused.';
      SQLDMOSvc_Running    :  sTmpStr := 'SQL Server on ' + svrlist.Item(i) + ' is running.';
      SQLDMOSvc_Starting    :  sTmpStr := 'SQL Server on ' + svrlist.Item(i) + ' state in transition from stopped to running.';
      SQLDMOSvc_Stopped    :  sTmpStr := 'SQL Server on ' + svrlist.Item(i) + ' is not running and cannot accept transactions.';
      SQLDMOSvc_Stopping    :  sTmpStr := 'SQL Server on ' + svrlist.Item(i) + ' state in transition from running to stopped.';
      SQLDMOSvc_Unknown    :  sTmpStr := 'Unable to determine SQL Server execution state.';
      else sTmpStr := 'Unable to determine SQL Server execution state.';
    end;
    serverlist.Add(sTmpStr);
end;
 SQLServer:=NULL;
 SQLServerStatus:=NULL;
 svrList:=NULL;
 result:=nServers;
end;

I have tested the above code, but you should get the gist of what I am trying to achieve...
   
0
 

Expert Comment

by:huiyue
ID: 9699730
I am sorry that I miss your problem.
I try,but the same trouble.
If you solute it ,don't forget telling me.
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 12694300
PAQed with points refunded (500)

modulo
Community Support Moderator
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
IExtractImage Delphi 14 198
Internet Explorer View Settings Question 15 105
Printing problem 2 80
how to change, disabled button color FMX ? 1 9
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now