Using SQL Notification Services (SQLNS) to transfer data

Using SQL Notification Services (SQLNS) to transfer data

Post by Chris Aitchiso » Sat, 01 Feb 2003 13:36:06


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.


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


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...


Chris Aitchison


1. Using SQL Server for IPC data transfers using global temp tables

Hello and good day.

Basically I have a process logging data at intervals but in addition it has
a list of constantly changing "real time" values (changing far more
frequently than the logged interval).  A secondary application needs to
display these values.  There are a huge range of options for getting the
data from application A to application B, however we've decided that a very
applicable choice would be to update a record in a global temporary table
and the second application can then read from this.  The only problem with
this is that it imposes a significant amount of overhead.

Is there a way to put a temporary table entirely in memory with no
persistence whatsoever?  (i.e. There is no need for it ever to be written to
disk)  This would be very valuable to us.

Thank-you very much!

2. Microsoft Foxpro & Visual Foxpro Accessing Informix

3. SQL 7.0 Data Transformation Services - Can not transfer all objects

4. pgsql-server/src backend/utils/init/postinit.c ...

5. Btrieve to SQL Server using Data Transformation Services and Data Direct ODBC Driver

6. updating a recordset from a sp

7. SQL SVR Notification Services

8. finding similar/same records between 2 files of same structure

9. send mail attachments via SQL Server Notification Services

10. SQL notification Services installation.

11. Problems creating multiple event classes -- SQL Server Notification Services

12. SQL Notification Services--events

13. Using ODBCCPY to transfer data from DBFs to SQL Server