USE [CarsemERP]
GO
/****** Object:  StoredProcedure [dbo].[DBA_CarsemERP_SQLBlocks]    Script Date: 04/01/2015 15:14:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<THARMENDRAN>
-- Create date: <2014FEB21>
-- Description:	TO CHECK ANY LOCKS MORE THN 20 SEC IN SQL SERVER
-- =============================================
ALTER PROCEDURE [dbo].[DBA_CarsemERP_SQLBlocks]
AS
BEGIN
DECLARE @iCnt As Int
DECLARE @ICnt2 As Int
DECLARE @SendEmail AS VARCHAR(3)

DECLARE @Temp_DetailReq1 TABLE (
			idx smallint Primary Key IDENTITY(1,1),
                        DBName varchar(30), RequestId Int, BlockingId Int, BlockedObjectName varchar(30),LockType varchar(30), RequestingText varchar(max), BlockingText varchar(max),
                        LoginName varchar (30), HostName varchar (30))

DECLARE @Temp_DetailReq2 TABLE (
			idx smallint Primary Key IDENTITY(1,1),
                        DBName varchar(30), RequestId Int, BlockingId Int, BlockedObjectName varchar(30),LockType varchar(30), RequestingText varchar(max), BlockingText varchar(max),
                        LoginName varchar (30), HostName varchar (30))

SET @SendEmail = 'NO'
SELECT @iCnt= COUNT(*) FROM DBA_SQLBlocksViewTharmen

          IF @iCnt > 0
		    BEGIN
			Insert Into @Temp_DetailReq1 
			Select DBName, request_session_id, blocking_session_id, BlockedObjectName,resource_type, RequestingText, BlockingTest, LoginName, HostName From DBA_SQLBlocksViewTharmen
             END   

       WAITFOR DELAY '00:00:20'

select @iCnt2= COUNT(*) FROM DBA_SQLBlocksViewTharmen

          IF @iCnt2 > 0
				BEGIN
				DECLARE @columnHeaders NVARCHAR(MAX)
				DECLARE @tableHTML NVARCHAR(MAX)
				DECLARE @body NVARCHAR(MAX)
					Insert Into @Temp_DetailReq2
					                    
                                        Select DBName, request_session_id, blocking_session_id, BlockedObjectName,resource_type, RequestingText, BlockingTest, LoginName, HostName From DBA_SQLBlocksViewTharmen
                                        SET @SendEmail = 'YES'
                                        BEGIN
                                            SET @columnHeaders = 'DBName</th><th>RequestId</th><th>BlockingId</th><th>BlockedObjectName</th><th>LockType</th><th>RequestingText</th><th>BlockingText</th><th>LoginName</th><th>HostName'
                                            set @tableHTML =
	                                    '<div><b>There is blocking in VERPSVR02-02.</b></div><br>' + -- This is the bold text at the top of your email
	                                    '<table border="0" cellpadding="5"><font face="Calibri" size=2>' +
	                                    '<tr><th>' + @columnHeaders + '</th></tr>' +
	                                     convert(nvarchar(max),
	                                    (
		                                  select td = [DBName], '',			-- Here we put the column names
				                                 td = [RequestId], '',
				                                 td = [BlockingId], '',
                                                 td = [BlockedObjectName], '',
                                                 td = [LockType], '',
                                                 td = [RequestingText], '',
                                                 td = [BlockingText], '',
                                                 td = [LoginName], '',
                                                 td = [HostName], ''		-- Here we put the column names
		                                  from @Temp_DetailReq2
                                          for xml path('tr'), type)) +'</font></table>'
END
END
                        IF @SendEmail = 'YES'
                        BEGIN
                        EXEC msdb.dbo.sp_send_dbmail
						@profile_name	= 'MyDBMailProfileName',
						@recipients	= 'itdba@xxx.com.my',
						@body		= 'Please refer the attachment' ,
						@body_format	= 'HTML',
						@subject	= 'Alert! Blocking On ERPSVR02-02 Live Server',
						@file_attachments = @tableHTML,
						@importance     = 'High';
						
						END
	END