cardax
asked on
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..
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..
ASKER
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):integ er;
var
SQLServer:Variant;
SQLServerStatus:Variant;
SvrList:Variant;
i,nServers:integer;
sRetValue,sTmpStr:String;
begin
SQLServer := CreateOleObject('SQLDMO.Ap plication' );
SQLServerStatus := CreateOleObject('SQLDMO.SQ LServer'); //added to obtain SQL Server Status
SvrList:= SQLServer.ListAvailableSQL Servers;
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...
function SQLServerList(var ServerList:Tstrings):integ
var
SQLServer:Variant;
SQLServerStatus:Variant;
SvrList:Variant;
i,nServers:integer;
sRetValue,sTmpStr:String;
begin
SQLServer := CreateOleObject('SQLDMO.Ap
SQLServerStatus := CreateOleObject('SQLDMO.SQ
SvrList:= SQLServer.ListAvailableSQL
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...
I am sorry that I miss your problem.
I try,but the same trouble.
If you solute it ,don't forget telling me.
I try,but the same trouble.
If you solute it ,don't forget telling me.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
it may be in the folder:Program Files\Microsoft SQL Server\80\Tools\Binn
then ,Try the following codes:
function SQLServerList(var ServerList:Tstrings):integ
var
SQLServer:Variant;
SvrList:Variant;
i,nServers:integer;
sRetValue:String;
begin
SQLServer := CreateOleObject('SQLDMO.Ap
SvrList:= SQLServer.ListAvailableSQL
nServers:=SvrList.Count;
serverlist.Clear;
for i := 1 to nservers do
serverlist.Add(svrlist.Ite
SQLServer:=NULL;
svrList:=NULL;
result:=nServers;
end;
Good Luck!
yuehua