SQLTeam.com | Weblogs | Forums

Run an executable when a specific table updated

sql2012

#1

Hello,

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.

Thanks for any help in advance.


#2

Does the exe just do what it says Inserts rows into some table?


#3

Yes.


#4

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())

#5

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*/

#7

How to set the job schedule to 2 second? The maximum number accepts is 10 second.


#8

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

DECLARE @MyTableVar table( NewScrapReasonID smallint,  
                           Name varchar(50),  
                           ModifiedDate datetime);  
INSERT Production.ScrapReason  
    OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate  
        INTO @MyTableVar  
VALUES (N'Operator error', GETDATE()); 

#9

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.


#11

Thanks for your help.


#12

Thanks yosiaz.