IF OBJECT_ID ( 'checkServerOnline', 'P' ) IS NOT NULL 
   DROP PROCEDURE checkServerOnline;
GO
CREATE PROCEDURE checkServerOnline
AS
BEGIN
	SET NOCOUNT ON;
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
	BEGIN TRANSACTION;
	DECLARE @OVER_TIME int;
	DECLARE @SV varchar(30);
	SET @OVER_TIME = 2;
	DECLARE @LockResult int;
	EXECUTE @LockResult = sp_getapplock @Resource = 'checkServerOnline',@LockMode = 'Exclusive',@LockTimeout = 0;
	    
    IF @LockResult <> 0
		BEGIN
		   ROLLBACK TRANSACTION
		   RAISERROR ( 51001, 16, 1 )
		   RETURN
		END

		DECLARE db_cursor_server CURSOR FOR 
		SELECT SERVER_NAME FROM CSC_SERVER WITH(NOLOCK) WHERE DATEDIFF(mi , LAST_ACTION , GETDATE() ) > @OVER_TIME;

		OPEN db_cursor_server  
		FETCH NEXT FROM db_cursor_server INTO @SV
		
		WHILE @@FETCH_STATUS = 0  
		BEGIN  
				
			BEGIN TRANSACTION;
				UPDATE CSC_SERVER SET SERVER_STATUS = '2' WHERE SERVER_NAME = @SV;
			COMMIT TRANSACTION;

			BEGIN TRANSACTION ;
				UPDATE CSC_QUQUE SET PROCESS_STATUS = CASE WHEN PROCESS_TYPE = 'R' THEN 'T' ELSE 'K' END WHERE SERVER_NAME = @SV AND PROCESS_STATUS = 'R';
			COMMIT TRANSACTION;

			BEGIN TRANSACTION;
				UPDATE CSC_QUQUE SET PROCESS_STATUS = 'B',SERVER_NAME = '' WHERE SERVER_NAME = @SV AND PROCESS_STATUS = 'T' AND PROCESS_TYPE != 'R';
			COMMIT TRANSACTION;
			
			FETCH NEXT FROM db_cursor_server INTO @SV
		END  
		CLOSE db_cursor_server;
		DEALLOCATE db_cursor_server ;

	EXECUTE sp_releaseapplock @Resource = 'checkServerOnline';
	COMMIT TRANSACTION;
END
GO