I have a windows service that constantly queries a table in DB1 and if any changes are found in it, it transfers those changes to DB2. For that, it constantly queries the DB1.
Is there any mechanism by which I can subscribe to an event in DB1 such that any changes in table is notified to the window service ?
The tables in DB1 are capture tables which have only the changed values.
Yes, there is such a mechanism, named Notification services, but it only valid for sql 2005, in 2008 you can use the well known other options:
which need not the intermediate services to run with, except the last one