How to run a windows service triggered by a change in the database?
-
Hi folks,
I have a database, which frequently gets update. Everytime it is updated, I want to run a windows service (or just an .exe file) to do something else. How do I do this?
Thanks in advance.
-
I don't like it, but a combination of a trigger and xp_cmdshell will probably help you get there.
Why do you need to do this? (There may be another way.)
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Just grasping at straws, but perhaps you could extend notification services to do something like this?
-
Could you set up a recurring DTS package to poll for a changed? Having it launch every 5 minutes or so and shutdown if nothing has changed?
-
I suspect you could use xp_cmdshell and the net start <servicename> command, although I haven't tried it. You could use net stop <servicename> to stop the service afterwards. Alternatively you could set up a Job to test a condition at regular intervals and then issue the net start command using a step of the CmdExec type.
I am not sure whether xp_cmdshell will wait for net start to return from starting the service before it continues, in which case there may be some delay before processing continues.
You would also need to take precautions to protect against issueing multiple commands to start and stop the service in rapid succession.
If you have a systems management tool such as HP-Openview or CA-Unicenter, you might consider generating an appropriate SNMP message and leaving the service management to the other system.
David
If it ain't broke, don't fix it...
-
/*
Create a "semiphore" table to communicate with called AfterUpdateProcess
Create a Trigger that notifies the "semiphore" table called tr_MyTable_Update
Create a Stored Procedure usp_Launch_Process that reads the "semiphore" table
and Launches the Process
Create a SQL Agent Job that checks for the update using usp_Launch_Process,
using the 'semiphore" table and determines whether that the update occured,
and if so reset the cycle in "semiphore" table and launch the process
using xp_cmdshell with a controlled account (no user access required)
*/
PRINT 'AfterUpdateProcess table'
if exists (select * from sysobjects where sysstat & 0xf = 3
and id = object_id('AfterUpdateProcess'))
drop table AfterUpdateProcess
GO
CREATE TABLE AfterUpdateProcess (
ID int IDENTITY (1, 1) NOT NULL ,
LastProcessDateTime datetime NULL ,
LastUpdateDateTime datetime NULL ,
CONSTRAINT PK_AfterUpdateProcess PRIMARY KEY NONCLUSTERED
(
ID
 
ON PRIMARY ,CONSTRAINT AK_AfterUpdateProcess UNIQUE CLUSTERED
(
LastProcessDateTime,
LastUpdateDateTime
 
ON PRIMARY) ON PRIMARY
GO
PRINT 'tr_MyTable_Update trigger on MyTable'
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'tr_MyTable_Update' AND type = 'TR')
DROP TRIGGER tr_MyTable_Update
GO
CREATE TRIGGER tr_MyTable_Update
ON MyTable
FOR UPDATE AS
SET NOCOUNT ON
IF UPDATE(MyFieldThatIsAlwaysUpdated)
BEGIN
UPDATE AfterUpdateProcess
SET LastUpdateDateTime=GETDATE()
END
GO
PRINT 'usp_Launch_Process to schedule with SQL Agent Job'
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.usp_Launch_Process') AND sysstat & 0xf = 4)
DROP PROCEDURE dbo.usp_Launch_Process
GO
CREATE PROCEDURE usp_Launch_Process
AS
SET NOCOUNT ON
IF EXISTS(SELECT * FROM AfterUpdateProcess
WHERE LastUpdateDateTime > LastProcessDateTime)
BEGIN
UPDATE AfterUpdateProcess
SET LastProcessDateTime=GETDATE()
EXEC xp_cmdshell 'MyProcess.exe', NO_OUTPUT
END
SET NOCOUNT OFF
RETURN (0)
GO
/*
The Schedule defined for the Job will determine the latency
between UPDATE and Launch Process
*/
You could add an offset to the LastProcessDateTime assignment to protect from flooding situations, a value just greater than the cycle period of the Job schedule would force a skip of a cycle.
Andy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply