October 16, 2008 at 6:42 am
Hi All
Can anyone please help me in creating script to check agent status on the all servers. I want to deploy the script on one my centralized servers. Till now i am ablle to create the following mentioned code :
DECLARE @server VARCHAR(50)
DECLARE @Type CHAR(1)
DECLARE @sql NVARCHAR(300)
Declare @result nvarchar(50)
DECLARE @ParmDefinition NVARCHAR(500)
TRUNCATE TABLE serveragentStatus
DECLARE sqlsrv_cursor CURSOR FOR SELECT Server_Name FROM ServerDetails Where Is_Active=1
OPEN sqlsrv_cursor
FETCH FROM sqlsrv_cursor INTO @server
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'EXEC @statusOUT=['+ @server + '].master..xp_servicecontrol'
SET @sql= @sql + ' ''QUERYSTATE'''
set @sql =@sql + ',''Sqlserveragent'''
SET @ParmDefinition = N'@statusOUT varchar(30) OUTPUT'
exec sp_executesql @sql,@ParmDefinition,@statusOUT=@result OUTPUT
Print @result
fetch next from sqlsrv_cursor INTO @server
END
CLOSE sqlsrv_cursor
DEALLOCATE sqlsrv_cursor
BUT WITH THIS THE OUTPUT "0" IS GETTING SAVED IN MY TABLE . I NEED TO STORE THE VALUES "RUNNING" OR "STOPPED" WHICH COMES AFTER EXECUTING THE TSQL PROC XP_SERVICECONTROL.
ANY HELP WOULD BE HIGHLY APPRECIATED.!!!!!!!!!!!!!!!!!!!!!!!!
October 16, 2008 at 7:04 am
If @result = '1'
select @result 'running'
else
select @result = 'stopped'
October 16, 2008 at 8:09 am
Hi Steve
Thanks for replying !!!!
Actaully sp_exectutesql always return (0) for success and (1) for failure.
In my case it is just returning (0) even if the agent on some particular server is stopped. Value (1) is not showing in the @result paramter.
October 16, 2008 at 9:41 am
Can anyone help out on this
October 16, 2008 at 10:32 am
Started playing with xp_servicecontrol a bit and it looks like it works fine for default installations, but how can you use it to control/monitor a named instance?
I haven't had much luck with my searches on the net as of yet.
😎
October 16, 2008 at 10:55 am
Figured it out. It was probably mentioned somewhere, but I had to use the Service Name, not the display name.
😎
October 19, 2008 at 3:21 pm
Try This One =>
create table #test(servicename varchar(50),status varchar(50))
insert into #test(status) exec master..xp_servicecontrol 'QUERYSTATE','Sqlserveragent'
update #test set servicename='Sqlserveragent'
select * from #test
October 20, 2008 at 11:44 am
Hi Tushar
Thanks for replying !!!!
I have executed this now :
DECLARE @server VARCHAR(50)
DECLARE @Type CHAR(1)
DECLARE @sql NVARCHAR(300)
Declare @result nvarchar(50)
DECLARE @ParmDefinition NVARCHAR(500)
--TRUNCATE TABLE serveragentStatus
create table #test(servicename varchar(50),status varchar(50))
DECLARE sqlsrv_cursor CURSOR FOR SELECT Server_Name FROM ServerDetails Where Is_Active=1
OPEN sqlsrv_cursor
FETCH FROM sqlsrv_cursor INTO @server
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'insert into #test(status) EXEC ['+ @server + '].master..xp_servicecontrol'
SET @sql= @sql + ' ''QUERYSTATE'''
set @sql =@sql + ',''Sqlserveragent'''
--SET @ParmDefinition = N'@statusOUT varchar(30) OUTPUT'
exec sp_executesql @sql
update #test set servicename='Sqlserveragent'
Print @result
fetch next from sqlsrv_cursor INTO @server
END
CLOSE sqlsrv_cursor
DEALLOCATE sqlsrv_cursor
select * from #test
It is throwing this error :
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply