SP_processmail for replication.

[cdassouk]

Is it possible to use sp_processmail procedure to send replication's data between 2 servers. At the moment, I can replicate 2 sql servers over the network. What I need is to send replication's files from one server to the other through e-mail.

I need some information about sending the result of replication jobs by e-mail.

Thanks.

 

[rajeevnandanmishra]

Hi there,

you should try to follow following steps.
-----------------------------------------
1) USE xp_readmail to read all incoming mails and then find out if their is any mail with subject 'execute myReplicate' Assumes that you had sent the replication files with the subject line as 'execute myReplicate' and . Pass @peek as true, so that it will not set any of the mail's status as read.
2) Now if there is any such mail then store its id. And copy the attachments to a predefined path with predefined name(s).
3) Now execute sp_processmail with @subject as 'execute myReplicate'.

You should write a new Stored Procedure on the server as
myReplicate. Which would run the replication commands and uses the path and filenames as stored above.
If everything goes fine then re-read the mail by using xp_readmail and passing the msgid as stored previously and @peek as false. This shows you that this mail is processed. And moreover it will not be processed next time.

----------------------------------------------------
Hope this will move you in right direction.