I like to use the following query to be run every 2 seconds. The query check the last user update for ORDR table every 2 seconds if returns a result then run the executable.
SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update --,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'LiveDB')
AND OBJECT_ID=OBJECT_ID('ORDR') and CONVERT(VARCHAR, last_user_update, 120) >= dateadd(S,-2,getdate())
The executable name and location is: \\SQL\app\InsertRows.exe
Please let me know if there are better ways to accomplish the same goal.
Note: I don't want to use trigger for this particular case.
One other way you could do it, is create a stored procedure that does the same thing that exe does, unless the exe does a whole more stuff.
create a sql job that calls that stored procedure and does the insert based on your logic
SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update --,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'LiveDB')
AND OBJECT_ID=OBJECT_ID('ORDR') and CONVERT(VARCHAR, last_user_update, 120) >= dateadd(S,-2,getdate())
Maybe something like below. Although you may want to reconsider every 2 seconds, that seems rather extreme.
IF EXISTS(
SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update --,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'LiveDB')
AND object_id = OBJECT_ID('ORDR')
AND last_user_update >= DATEADD(SECOND,-2,GETDATE())
)
BEGIN
EXEC sys.xp_cmdshell '"\\SQL\app\InsertRows.exe"'
END /*IF*/
another option is the following, capturing insert, update deletes using the OUTPUT . depends how are implementing your Insert update deletes. just another option.
even if the job cannot run every 2 seconds, another option is to keep track of things in a log tracking table that shows you when the last INSERTROWS happened
The only way I can think off the top of my head is to schedule the main job for every 10 seconds. The first thing that job did is start 4 other jobs: the first thing the first job does is wait 2 seconds -- WAITFOR DELAY '00:00:02.000' -- then run; the second jobs waits 4 seconds, then runs; the third job waits 6 secs, etc..
But, again, I'd recommend backing off 2 seconds and going to 10 seconds. If you really need to see activity to the second or two, use some type of cdc or trace.