[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 167
  • Last Modified:

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

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
cardax
Asked:
cardax
  • 2
1 Solution
 
huiyueCommented:
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
 
cardaxAuthor Commented:
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
 
huiyueCommented:
I am sorry that I miss your problem.
I try,but the same trouble.
If you solute it ,don't forget telling me.
0
 
moduloCommented:
PAQed with points refunded (500)

modulo
Community Support Moderator
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now