Writing Replication to a text file and send it by mail.

[cdassouk]

I can send the result of a query as attachement to an e-mail message by running the following script:

EXEC xp_sendmail @recipients = 'chadi',
@query = 'SELECT * FROM Vmdb2..Customers',
@subject = 'Customers',
@message = 'The contents of Customers',
@attach_results = 'TRUE', @width = 250

I need to set up the following replication scenario:

Transactional replication between 2 sites.
I want to replicate data by using Microsoft Exchange 2000 so data will be extracted from the source database tables then it will be attached to an e-mail message and then sent to the destination server through Microsoft Exchange Server 2000. The destination server will read the e-mail message and extract the data from attached file and then insert it to the destination database tables.

Could anybody provide some help about this issue. Especially about writing Transactions marked for replication in one database to a text file or any other file formats.

Many thanks

 

[aalmeida]

It all seems to be to complicated. What keeps you from using regular replication routines?
It seems to me that you will have to export a query to a text file and then send an e_mail, to identify what is to be sent at the current time (all that has not been sent since last time you queryed the db) now here is the main question, how to identify what needs to be sent? you could use a time stamp column (it has the advantage that is unique for each Db) and after exporting just e_mail it and process it at the receiving server
To export to a text file you can use the BCP Utility
Or use the xp_sendmail (it has restrictions)
AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[cdassouk]

Thanks a lot for your comments.

You are right, how to identify what needs to be sent?

Actually, I found that this option is available in Sybase but it is not available in SQL Server. In Sybase you can set up a message based replication that allows replication between 2 databases that have no direct connection. All updates are sent as messages and saved in a folder on the hardisk.

I need to know if this is possible by using MS SQL Server 2000. in other way, I need to know if I can extract and write all data marked for replication in a database to the hard disk. Once all updates are in one file, I can attach this file to an e-mail message and then send it to the destination server where I have to insert it in the destination database.

Many thanks,

Chadi

 

[aalmeida]

Yes! I think it is possible to do all that, the only if is how will you mark whatever needs to be sent, once you have that very well on specs, the next steep is select it.
Than you may put it all as attatchment to e_mail and send it.
SQL 7 and 2k is very well able to send and receive emails as well as process records send and received by emails, so that is covered. AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"