/*

Author 				: Kuldip Rindani
Create Date			: 01-APR-2009
Purpose				: For Dispatching Sample Alert using AlertDispatcher.exe
Dependencies			: Script uses xp_cmdshell to execute a Exe.
				  therefore, it should be enabled on machine were monitoring is setup.
Modification History		:

*/


Declare @Row_Count int
Declare @ExePath varchar(255), @varMsg varchar(255), @varCmd varchar(2000)
Declare @IPAddress varchar(25), @Port varchar(10) 
--Path for exe Need to customized as per site setup.
Set @ExePath = 'C:\Monitoring\AlertDispatcher.exe'


--Remember below is sample query for monitoring, your could be different than it.
--Below query is for checking if any Data is present is table for filter columns.
select @Row_Count  = Row_Count from OpenQuery( LinkName, 
'

Select count(*) as Row_Count from Your_Database..Your_Tables with (nolock) where Your_Columns = ''SOMEVALUE''
')


Set @varMsg = 'Date : ' + CAST(GETDATE() as varchar(50)) + ', Alert for Your_Database..Your_Tables on Your_Server  = ' + CAST(Row_Count as varchar(10)) 

--Below is sample logic for send Alert when data is present in Your_Database..Your_Tables
IF @Row_Count > 0 
BEGIN
	--Loop through each Registered Alert Receiver and dispatch the message
	Declare C1 cursor For select IPAddress, Port from MonitorIP where sent_flag = 1
	Open C1
	Fetch Next From  C1 InTo @IPAddress, @Port
	While @@Fetch_Status <>-1
	Begin
		--Create the String for executing AlertDispatcher.exe utlity.
		set  @varCmd = @ExePath + ' ' + @IPAddress + ' ' + @Port + ' "' + @varMsg + '"'      

		exec master..xp_cmdshell @varCmd    

		Fetch Next From  C1 InTo @IPAddress, @Port
	End
	Close C1
	Deallocate C1
END