Notifying multiple applications of table update

[James Chang]

Hello,

I am writing an application using Visual Studio .NET (C#). The
application will be run on several workstations simultaneously, and
all of them will be accessing a single sql server database. I am
trying to have it so that when one of the applications updates the
fields of a table in the database, all of the other applications are
notified (Without polling the DB).

I have been trying to do this by using a SQL Trigger and calling
RaisError from within. This will create an exception for the calling
application (what i wanted), but I can get any of the other
applications to see the error.

Is there a way of accomplishing this using triggers? Is there another
way of notifying all applications of a database change without the use
of polling?

James Chang
james.chang@que st.com.au

[Erland Sommarskog]

Re: Notifying multiple applications of table update

[posted and mailed, please reply in news]

James Chang (james.chang@qu est.com.au) writes:[color=blue]
> I am writing an application using Visual Studio .NET (C#). The
> application will be run on several workstations simultaneously, and
> all of them will be accessing a single sql server database. I am
> trying to have it so that when one of the applications updates the
> fields of a table in the database, all of the other applications are
> notified (Without polling the DB).
>
> I have been trying to do this by using a SQL Trigger and calling
> RaisError from within. This will create an exception for the calling
> application (what i wanted), but I can get any of the other
> applications to see the error.
>
> Is there a way of accomplishing this using triggers? Is there another
> way of notifying all applications of a database change without the use
> of polling?[/color]

You will somehow have to signal to the clients that something has
happened. This is far from a trivial endeavour. You must either write
an extended stored procedure for the task, or write a COM object that
you communicate with the sp_OAmehotds. In either case, you need to
test what your write very carefully, because if you get an execution
error in the code you write, you can bring down the entire SQL Server.

Furthermore, if you actually take the task to signal the other
clients from the XP/COM-object directly, then you will prolong the
execution time for the trigger considerably, and this will have an
impact on concurrency in the system. So, in practice you should
just leave a quick note that does not require acknowledgement , and
then you have an other process which reads these notes, and signals
the processes.

You may also take a look at SQL Server Notification Services, (see
www.microsoft.com/sql) and see if this is something can meet your
needs.

In the end you may find that polling is the simplest, safeest and the
most robust method for you to use.
--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp