Application Notification

Question

Application Notification

gbelin_saiz

Newbie

Points: 1
More actions
October 20, 2003 at 1:35 am

#81242

Hi there,
I have an application that is polling a table periodically in order to check the status os some devices, this table is updated by another application, I am using the database as a mecanism to communicate processes.
I would like to replace the polling process by a trigger based notification method, I mean, I would like to set a trigger that depending on the content of an updated field, the trigger notifies the application, so no polling is needed and this overhead is erased.
I have been searching for a way to implement this, but I have not found anything, Have anyone an idea of how to implement this?
kind regards.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

ray_higdon SSCarpal Tunnel Points: 4589 More actions · Answer 1

You can use xp_sendmail to send email notifications in a trigger, as far as notifying an application depends on what the app requires.

Ray Higdon MCSE, MCDBA, CCNA

------------

Ray Higdon MCSE, MCDBA, CCNA

Allan1 SSC-Addicted Points: 446 More actions · Answer 2

I have a similar need. In my case, I'm running SQL Server 2000, Visual Basic 6.0 (and others are using Visual C++, as well). I need to set triggers on various tables. When the data in any of those tables are changed, I need to notify the running VB 6 app, so that it can AUTOMATICALLY (!!) refresh itself. Probably for 10-50 users, max (It depends on which table, and who the user is - rights, etc). Anyone have additional suggestions (i.e. best way to capture the email in VB?, etc.).

Thanks,

Allan

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

quote:
Anyone have additional suggestions (i.e. best way to capture the email in VB?, etc.).

not sure if I understand this right.

Are you looking for a way to check your inbox (or another folder) from within VB for existance of a certain mail?

Frank

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

guarddata SSCertifiable Points: 6583 More actions · Answer 4

You could use either windows messaging or .NET remoting in the VB app. The SQL trigger could perform xp_cmdshell to a program with enough parameters to know the message to send. Not especially elegant, but pretty easy to create.

Guarddata-

Allan1 SSC-Addicted Points: 446 More actions · Answer 5

What happens in similar systems (which we are trying to improve in this new app) is: 1)Data is modified in SQL Server table. 2) Either a) App polls SQL Server every n seconds (n is usually 30). OR b) SQL trigger sends message to users - telling them to refresh their screens.

Proposed new system: 1) Data modified in SQL table. 2) SQL trigger sends message to VB app. 3) VB app automatically refreshes data on any applicable open screens.

The idea here is three-fold - 1) Eliminate annoying the user by asking him/her to press Refresh button. 2) Eliminate needless polling [Refresh] (since not every possible table will need to be refreshed). and 3) Speed the process up enough to be faster than continuous polling.

I need to find the best way to send info from SQL Server (based on triggers) to VB 6 (I'm using ADO with this), to avoid constant polling or messages to the user.

(By the way, I believe that some of the messages to the user to refresh are based on timers in the app - not on messages from SQL).

Hope this makes sense.

Thanks,

Allan

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 6

An idea, don't know how feasible, since I don't work with MSMQ a lot.

What about writing extended stored procedures that would drop in a message queue (MSMQ) or buy a similar product? If the apps are checking the message queue, they could see the message and respond accordingly. Could something like that work?

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/

K. Brian Kelley
@kbriankelley

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

quote:
What about writing extended stored procedures that would drop in a message queue (MSMQ) or buy a similar product? If the apps are checking the message queue, they could see the message and respond accordingly. Could something like that work?

if you want to go down MSMQ road this might be of interest

http://www.tagconsulting.com/Show.asp?Id=1000

No need to write an extended stored procedure!

Frank

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 8

True, but this uses sp_OA and that's generally frowned upon... because if you give access to a non-admin user, that user could potentially instantiate any COM+ object on the server.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/

K. Brian Kelley
@kbriankelley

Frank Kalis SSC Guru Points: 111183 More actions · Answer 9

Yes, from the security point of view, you're right!

Frank

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]