Very Computer

I have recently been assigned the task of transferring
data between two company's sql server database.  The two
databases are used for different tasks - however there is
a common theme in some of the data, and this is the reason
why a transfer is neccessary.

Developers at each company are responsible for writing the
code that will determine what data the other company
requires, sending the data to them (via XM web service)
and keeping a transaction log for easy rollback if
needed.  This means that when I send data to the other
company I need to get a response which will usually be
some flags indiciating succes, and any returned primary
keys from their database for cross-referencing information
between the two databases.  I am the developer for one of
these companies.

I have figured out how to send the data to the other
company, and also all the transaction logging and general
application framework will not be a problem.

My question is determining "when" I need to send the other
company my information.  I am looking at using SQL Server
Notifications Service for this as it seems to be designed
for my needs (from reading the docs for an hour or so).

I can design the events that will cause a notification to
be generated, but I do not want my notification to deliver
an e-mail.  I would like one of the following to happen:

1. SQLNS calls the other company's web service and gives
them the updated data directly.  I assume that I would
derive a class from IDeliveryProtocol to acheive this, but
would this cause any system delays?  the call to the other
company's web service may take a short period of time and
it is important that I get any returned data from them and
update the local database.

or

2. SQLNS sends an MSMQ message to a service I write which
then goes to the database and retrieves the data and sends
it to the other company.  This would allow SQLNS to carry
on with its tasks without waiting for the response as it
is in a different process

or

3. SQLNS drops a file in a directory and a service I write
sees the drop and sends the data from this file to the web
service of the other company.

I realise that 2 and 3 are very similar, but is there any
advantage in one over the other

I also realise that my thoughts may be unclear, but I am
still trying to get my head around SQLNS and if it really
suits my needs here.  I remember going to a SQLNS session
at MEC 2002 in Anaheim, and thinking that this is cool -
but I have since forgotten most of what was said.

If there is anyone out there who consider themselves
knowledgable in SQLNS and are willing to give me pointers
I would really appreciate it, or even if you could point
me in the right direction to get help...

TIA

regards,
Chris Aitchison