-- The purpose of this script is to create a text report to be emailed showing which tables passed or failed replication
-- in this case each publication containes one article which containes one table   
-- Tested on sql server version 7.0

/* Step (1)  create a stored proc on the publisher to perform the validation */

CREATE PROCEDURE r_validation
AS
/* Use to validate replication on all subscribers, Output will appear in the error log and sysreplicationalerts table */

EXEC sp_publication_validation atbl

EXEC sp_publication_validation btbl

EXEC sp_publication_validation ctbl

EXEC sp_publication_validation dtbl

-- etc, etc, etc for as many publications as you need  
-- When you execute a sp_publication_validaion stored proc, there is no waiting for a return value like other procs
-- It goes off does its thing and then puts the results in the error log.
-- But there is another place where you can get the results and that's in the msdb..sysreplicationalerts table

/* Step (2) create a stored proc on the publisher which you will use to create the report */
-- Note WHERE clause needed so you do not include results from a previous days run
  
CREATE PROCEDURE r_ReplReport
AS
select RTrim(subscriber) + " " + RTrim(publication) + " " +  
CASE alert_error_code
WHEN 20575 THEN "Passed"
WHEN 20574 THEN "Failed"
END
+ " " + CONVERT(Varchar(12),time)
FROM msdb..sysreplicationalerts
WHERE (alert_error_code = 20575 OR alert_error_code = 20574) AND time > CONVERT(Varchar(12),Getdate(),110)
ORDER BY publication, subscriber


/* Step (3) create a job to schedule and run the r_validation stored procedure */
-- Example job name : SaturdayValidation
-- Step 1: Validate RICHIES_DB Tables
-- Type: TSQL
-- Database: RICHIES_DB
-- Command: EXEC r_validation


/* Step (4) create a job to create and email the report. This is the tricky bit as you will have to estimate when to run it as you have to allow time
** for all the validations to be completed before creating the report. In other words suppose you run the SaturdayValidation job at 5:00am
** saturday and it takes 2 hours. Then you would schedule the ValidationTxtFile job sometime after 7:00am. The report is sent as an attachment */

-- Example job name: ValidationTxtFile
-- @step_id = 1, @step_name = N'Create Text File', @command = N'osql -E -Q "EXEC RICHIES_DB..r_ReplReport" -S DSERVER -h-1 -n -o f:\Validation.txt  
-- @step_id = 2, @step_name = N'End Email with Attached text file', @command = N'exec xp_sendmail @recipients = ''DBAs'',  
-- @subject = ''DSERVER Replication Validation Report'',
-- @message= ''Attached text file contains results'',
-- @attachments = ''f:\Validation.txt''