We help IT Professionals succeed at work.

Merge Validation Notification

Hi.
I need to schedule a job to do Merge validation for all my subscribers, then send me the results to my E-Mail.
How this can be done using SQL Server 2008?
Comment
Watch Question

Commented:
Schedule a agent job to do the following

use [AdventureWorks2008]
exec sp_helpmergepublication @publication = N'test'
go
exec sp_validatemergepublication @publication = N'test', @level = 1                        
and store your results in a history table and email yourself the data you want

Author

Commented:
Thank you for your reply.
but I prefer not to create any tables for this task so I searched SQL Server and I have found 2 alerts called: "Replication: Subscriber has failed data validation" and "Replication: Subscriber has passed data validation" and I have enabled the option to notify an operator in both alerts, but I am not receiving any E-Mail notification when one of these alerts is met.
P.S: I can send E-Mail successfully from the database.

Commented:
Alerts are based on the SQL error log ..  is what you are trying to alert from in the log ?
 

Author

Commented:
Alerts for the "Replication: Subscriber has passed data validation" with Error Code 20575 is being stored in [msdb].[dbo].[sysreplicationalerts] table, but the alert for "Replication: Subscriber has failed data validation" with Error Code 20574 is not stored.
But both alerts are not being triggering the E-Mail send.
Gary PattersonVP Technology / Senior Consultant

Commented:
See if this configuration article helps you:

http://www.mssqltips.com/tip.asp?tip=1523

- Gary Patterson

Author

Commented:
The mentioned article talks about how to create an operator & send notification to this operator which is working fine for me, but the problem is how to notify the operator when the merge agent fails data validation!!
Gary PattersonVP Technology / Senior Consultant

Commented:
1) Is the machine that you are monitoring configured as a Replication Distributor, Publisher, or Subscriber?  You need to monitor for these on a Publisher or Distributor.

2) Did you create SQL Server Event Alerts for messages 20574 and 20575?  If so, make sure the ENABLE box is chacked to enable the alert:

http://msdn.microsoft.com/en-us/library/ms175076.aspx

3) When you test, do you see events 20574 and 20575 on the SQL Server Event Log?

- Gary Patterson

Author

Commented:
  1. I am monitoring it on the server that hosts both the publisher & the distributer.
  2. Both Alerts are enabled & set to notify an operator.
  3. For Failed validation: These two events are stored in SQL Server Log:
    1. Message: Error: 14151, Severity: 18, State: 1.
    2. Replication-Replication Merge Subsystem: agent xxxxxxx failed. Data  validation failed for one or more articles. When troubleshooting, check  the output log files for any errors that may be preventing data from  being synchronized properly. Note that when error compensation or delete  tracking functionalities are disable
  4. For passed validation: I am not getting any logs for it!!
   
VP Technology / Senior Consultant
Commented:
SQL Server Agent is responsible for sending alerts, and for alerts based on error messages, it monitors the error log.

By default, only messages with a severity of 19-25 are automatically written to the error log.  You can force a lower severity message to be logged by changing it's sysmessages table dLevel value to WITH_LOG (128) using the sp_altermessage procedure.

You can check the severity of anymessage by querying sysmessages:

select * form sysmessages where error=14150;
You'll see, for example, that message 14150 is a severity 10 message" -  "Replication-Replication Merge Subsystem: agent xxxxx succeeded...".  

To alter it to always be logged:

sp_altermessage 14150, 'WITH_LOG', 'true';
Now you can set up alerts that will fire on this error message.

- Gary Patterson

Author

Commented:
when I try to run this command:
sp_altermessage 14150, 'WITH_LOG', 'true'
I get this error:
Msg 15277, Level 16, State 1, Procedure sp_altermessage, Line 31
The only valid @parameter_value values are 'true' or 'false'.
I checked BOL, the syntax is correct!! what could be wrong?
Gary PattersonVP Technology / Senior Consultant

Commented:
No idea.  I can only reproduce that error by misspelling "true".  

Try dropping the single quotes around "true".

- Gary

Author

Commented:
I managed to execute the statement ( I just changed 'true' to 'TRUE' ), but after executing the statement for the alerts: 14150,14151.20574,20575 , the severity field is not changed for them!!!
Here are the results after executing the statements:
message_id      language_id      severity      is_event_logged      text
14150      1033      10      1      Replication-%s: agent %s succeeded. %s
14151      1033      18      1      Replication-%s: agent %s failed. %s
20574      1033      10      1      Subscriber '%s' subscription to article '%s' in publication '%s' failed data validation.
20575      1033      10      1      Subscriber '%s' subscription to article '%s' in publication '%s' passed data validation.

Commented:
you could try something a bit differetn try to catch the error when executing your procedures

Create a procedure ...

use [AdventureWorks2008]
exec sp_helpmergepublication @publication = N'test'
exec sp_validatemergepublication @publication = N'test', @level = 1    
SQL Statement
Set quoted_identifier off
Go
Use tempdb
go
BEGIN TRY
exec Myproc
exec Myproc2
END TRY
begin catch      
select        
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;
end catch  
go


Run you proc as part of a scheduled job ....  the results\error you define you shoudl see them in the log ...

But Garys solution should work ?
Gary PattersonVP Technology / Senior Consultant

Commented:
That's because sp_altermessage doesn't change the severity of the message:  It changes the message's "WRITE_TO_LOG" property to force it to be written REGARDLESS of severity.

Query the sysmessages table before and after running the proc and you'll see what I mean.

- Gary Patterson
Gary PattersonVP Technology / Senior Consultant

Commented:
It's confusing - I agree.

Author

Commented:
It seems that now something else is preventing the notification: the event is logged ( I can see it in SQL Server Logs) and the Alert is fired ( Alert Properties --> History ---> Number of occurences: increases for each alert ) , but the E-Mail is not sent !! I tried to send a test E-Mail ... it is working!!
Gary PattersonVP Technology / Senior Consultant
Commented:
Wipe and reload.
Gary PattersonVP Technology / Senior Consultant

Commented:
Wipe and reload.
Gary PattersonVP Technology / Senior Consultant

Commented:
Seriously, though sounds like something is wrong with alerts.  Is is just these events, or can you not get email alerts on any events?
Gary PattersonVP Technology / Senior Consultant
Commented:
Here's a very good article on troubleshooting email Alerts:

http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/

- Gary Patterson

Author

Commented:
I reconfigured all Database Mail & Alerts configurations, and now it is working fine.
Thank you for your help.
Gary PattersonVP Technology / Senior Consultant

Commented:
Great!  Happy to help.

- Gary