Solved

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

Posted on 2003-11-02
5
160 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
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…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

763 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