I'm setting up replication using a "pull" approach for the updates where the "duplicate" server pulls the changes from the live database and applies them to the duplicate one on a rather basis. That part of it is working as expected.  

What I'm hoping to pick my fellow EDB developers brains on is how to let the database administrator know if there is a problem on the "duplicate" side of things.  Just say for example that that there is a problem loading the updates, how do I send an email or some other type of notification to someone to let them know that there is a situation that needs to be looked at from within the job itself?

Any and all suggestions would be appreciated.

Terry,

<< What I'm hoping to pick my fellow EDB developers brains on is how to let the database administrator know if there is a problem on the "duplicate" side of things.  Just say for example that that there is a problem loading the updates, how do I send an email or some other type of notification to someone to let them know that there is a situation that needs to be looked at from within the job itself? >>

You can trap any exceptions in a BEGIN..EXCEPT block in the job itself, and then call a database-specific external procedure to send an email if there's an issue.  You just have to make sure that you issue a USE DATABASE xxxx before trying to call the external procedure.

You can also do this in the database itself by adding ERROR LOAD UPDATE triggers to every table that will be receiving updates:

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=CREATE_TRIGGER

ERROR LOAD UPDATE triggers are a bit more flexible because they allow you to do things like log the row values that experienced an issue, retry the load operation, etc.

Tim Young
Elevate Software
www.elevatesoft.com

Thanks for the reply Tim,

> You can trap any exceptions in a BEGIN..EXCEPT block in the job itself, and then call a database-specific external procedure to send an email if there's an issue.  You just have to make sure that you issue a USE DATABASE xxxx before trying to call the external procedure.

The USE statement is actually the cause of the problem I'm running into.  I've set it up such that the replication can "self heal" by simply resetting the replication  process from the source side.  The target job will pull a clean backup from the source, do a restore, delete any pending update files, and just continue the replication on it's merry way.

I do have a procedure for sending email from the replicated database, but  as soon as I drop a USE statement into the job to connect to the target database I can't get exclusive access to do the restore if it's needed.   It's a classic chicken and the egg scenario.

I'm thinking that my best option with what I have available at this point is to create an in memory database which has access to the external email procedure and using that for sending the email notification.  It should work well, and it's easy to set up.  Was just hoping that someone had a suggestion that I hadn't thought of.

Thanks again for the response Tim.

Terry,

<< The USE statement is actually the cause of the problem I'm running into.  I've set it up such that the replication can "self heal" by simply resetting the replication  process from the source side.  The target job will pull a clean backup from the source, do a restore, delete any pending update files, and just continue the replication on it's merry way.

I do have a procedure for sending email from the replicated database, but  as soon as I drop a USE statement into the job to connect to the target database I can't get exclusive access to do the restore if it's needed.   It's a classic chicken and the egg scenario. >>

The USE DATABASE statement, by itself, will not cause a database open until the USE DATABASE statement is executed.  Is that the issue (that you're trying to do the restore *after* the USE DATABASE) ?

Tim Young
Elevate Software
www.elevatesoft.com