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,
    bwp
  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
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    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