?
Solved

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

Posted on 2003-11-02
5
Medium Priority
?
166 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

650 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