SQL Server Performance

can I know when another app modifies a table?

Discussion in 'Non-Transact SQL Developer Performance Tuning' started by bwperrin, Dec 11, 2004.

  1. bwperrin New Member

    Hi, I have a C# app that reads appointment data (using OleDbConnection to access .dbf tables) that another app writes to. I don't own the other app, so I can't create triggers or anything like that. Is there a way for my app to know when a table has been modified? If not, how do I get my app to periodically re-query (maybe every 15 seconds)?

    Thanks much,
  2. Argyle New Member

    If there is no way that you can add triggers or get them to enter a "last modified" date in the database/tables then you can't see when the data was last modified.

    How to get your app to requery depends on your app. You could put code in a loop that checks if new data has arrived or you could create a windows service that runs in the background and checks it.
  3. satya Moderator

    How about using SQL Server scheduled jobs, using this C# application calling command line operation.

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  4. bwperrin New Member

    the db table (appt.dbf in this case) does have a lastmodified field, but I still hate the thought of running a query on that big table so often. Is there something like a fileopen event message from the system that I can trap?

    Also, how to I set up a loop in my .NET app to keep checking without locking up the system? thx
  5. brimba New Member

    If you cannot setup a scheduled job in SQL-Server then I suggest that you buld a windows service that can run on some server to check that for you.

    If you just care about new inserts/deletes then you could do "count() and max()" on the table and that wont be that bad in a performance perspective. If you however need to check for updates as well then I suggest you to do sum(len(yourTextfield)) and check that value.

    AIf you need more "bulletproof" solution, then I recommend you to recheck the possibilites of using triggers.

    I know that SQL-Server 2005 will provide a procedure to check if the table data has been modified, but that wont solve your problems today.

Share This Page