Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2006

    Question Unanswered: sp_sendmsg issue


    I am planning to use sp_sendmsg for notification process. Everything works fine except one issue which I hope someone can help me with -

    I have a table X and a trigger on this table to send message using sp_sendmsg on insert/update. There is nothing else in the trigger.

    create table X ( id numeric(10,0) identity, name varchar(20))

    create trigger iu_X on X for insert, update
    declare @msg char(20)
    select @msg = convert(char(20), id) from inserted
    exec sp_sendmsg <ip>,<port>, @msg

    sending message part works perfectly well, but when the app ( C++ ) receiving the msg goes back to select the record immediately after receiving the msg, it can not find the record. When we put a wait of about 20 ms in the app between receiving the msg and doing a select it can see the record.

    I want to know why do we have to put a wait in the code? is there an explanation for this behavior? 20ms is just an arbitrary number which I do not like ;-).

    since there is no explicit tran, record should commit immediately after trigger sends a msg.

    Thanks in Advance,

  2. #2
    Join Date
    Jan 2003
    Geneva, Switzerland
    It's a perfectly normal behavior:

    The sp_sendmsg runs before the insert is committed (the data in the inserted table is the non-committed data) and the data will only be committed after the sp_sendmsg completes. The write to the database device will also take longer than the UDP message send, so you obviously have to delay a little bit in the client to get the at the data in the database.


  3. #3
    Join Date
    Aug 2006
    Thanks Michael,

    How do I determine the correct value for the delay? As I understand commit time will depend on other factors(disk i/o), So it is possible that client may not see the data even after 20ms delay. Do you think RTDS (Real Time Data Service) will have same issues?


  4. #4
    Join Date
    Sep 2002
    Sydney, Australia

    1. This is a hideous use of both sp_sendmsg and of a trigger. And thus the issues re use of uncommitted rows. With such a design, everytime you clear one obstacle (eg your post), the next obstacle will become visible. Think about exec sp_sendmsg outside the context of a transaction, outside the app is even better.
    2 RTDS is far superior, but it is at the high end of the performance scale.
    3 There are many options in-between. A common one is the (writing) app inserting a row in a queue table (no trigger); a separate mail-processing app runs as a background unix task on the server, and selects new rows from the queue table. The performance issues you need to consider are:
    - polling the queue (undesirable but common)
    - fetching the row, sending mail, updating (or deleting) the row in a transaction
    - some designs for queues actually use heap tables with no index, ie. set rowcount 1, and it is FIFO; I prefer a clustered index with one row per page

    The great advantage RTDS has over the queue tables most of us used before its advent, is that it uses Sybase internal structures to be awakened (eg. upon insert to a table) and does not poll. HP but $$$.

    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts