Solved

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

Posted on 2003-11-02
5
156 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
PAQed with points refunded (500)

modulo
Community Support Moderator
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

744 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

9 Experts available now in Live!

Get 1:1 Help Now