At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.
Trigger and xp_cmdshell
I have a trigger to detect when a certain column is updated to become a certain value. When triggered, I need to run an external program.
A sequence like:
CREATE TRIGGER TR_Inquiry_u
ON Inquiry
FOR UPDATE
AS
IF UPDATE( nInqStatus ) BEGIN
IF ( SELECT nInqStatus FROM inserted ) = 7 /* QS_Rcvd*/ BEGIN
DECLARE @sCmd varchar(256)
SET @sCmd= 'echo>c:\temp\test.txt HI there!'
EXEC master..xp_cmdshell @sCmd, no_output
END
END
GO
...works just fine, but I don't need to run the echo command. I need to run a program that will open and access the database and it must access the trigger table. That table is locked -- presumably because the trigger is still active.
Is the xp_cmdshell the right way to run an external program? Is there a better way?
Is there a way to make the external command run asynchronously, or to end the trigger's lock, or something -- so that the external program can access the trigger table? If so, please describe in detail.
-- Dan
A sequence like:
CREATE TRIGGER TR_Inquiry_u
ON Inquiry
FOR UPDATE
AS
IF UPDATE( nInqStatus ) BEGIN
IF ( SELECT nInqStatus FROM inserted ) = 7 /* QS_Rcvd*/ BEGIN
DECLARE @sCmd varchar(256)
SET @sCmd= 'echo>c:\temp\test.txt HI there!'
EXEC master..xp_cmdshell @sCmd, no_output
END
END
GO
...works just fine, but I don't need to run the echo command. I need to run a program that will open and access the database and it must access the trigger table. That table is locked -- presumably because the trigger is still active.
Is the xp_cmdshell the right way to run an external program? Is there a better way?
Is there a way to make the external command run asynchronously, or to end the trigger's lock, or something -- so that the external program can access the trigger table? If so, please describe in detail.
-- Dan
Asked:
4-
2
1 Solution
Thanks nigelrivett,
Do you suggest perhaps, making a new table, say CmdQueue, and in my TR_Inquiry_u, insert the command into CmdQueue? Then periodically run the commands I have queued?
I was hoping for something a bit more realtime. Could you show me what a continuously-running SP would look like and how to start it? Are there performance considerations?
How about if I put an INSERT trigger on this hypothetical CmdQueue table? Would that trigger be executed while still in the TR_Inquiry_u trigger? Would the Inquiry table continue to be locked if I ran xp_cmdshell from this CmdQueue trigger?
-- Dan
Do you suggest perhaps, making a new table, say CmdQueue, and in my TR_Inquiry_u, insert the command into CmdQueue? Then periodically run the commands I have queued?
I was hoping for something a bit more realtime. Could you show me what a continuously-running SP would look like and how to start it? Are there performance considerations?
How about if I put an INSERT trigger on this hypothetical CmdQueue table? Would that trigger be executed while still in the TR_Inquiry_u trigger? Would the Inquiry table continue to be locked if I ran xp_cmdshell from this CmdQueue trigger?
-- Dan
The inquiry table would still be locked as the sql statement would not complete until all triggers have completed - the server will roll back everything if there is a failure so it has to lock everything until returning from the statement.
You could do an sp_startjob in the trigger which would be fairly real time.
You could set the continuously running sp to start on server startup or just schedule it to to run every 5 mins or so - this means that if it aborts it will get restarted.
sp looks smething like
as
declare @cmd varchar(1000) ,
@id int
while 1 = 0
begin
select @id = null
while @id is null
select @id = min(id) from cmdtbl where status = 0
if @id is null
waitfor delay '00:00:05' -- wait 5 secs
endif
select @cmd = cmd from cmdtbl where id = @id
update cmdtbl set status = 1 where id = @id -- in process
exec (@cmd)
update cmdtbl set status = 2 where id = @id -- complete
end
go
You might want to hold the max id and wait for another one rather than use the status in case this table gets large and needs an index - otherwise have a daily archive job running to keep the table small.
You could do an sp_startjob in the trigger which would be fairly real time.
You could set the continuously running sp to start on server startup or just schedule it to to run every 5 mins or so - this means that if it aborts it will get restarted.
sp looks smething like
as
declare @cmd varchar(1000) ,
@id int
while 1 = 0
begin
select @id = null
while @id is null
select @id = min(id) from cmdtbl where status = 0
if @id is null
waitfor delay '00:00:05' -- wait 5 secs
endif
select @cmd = cmd from cmdtbl where id = @id
update cmdtbl set status = 1 where id = @id -- in process
exec (@cmd)
update cmdtbl set status = 2 where id = @id -- complete
end
go
You might want to hold the max id and wait for another one rather than use the status in case this table gets large and needs an index - otherwise have a daily archive job running to keep the table small.
NB you might want to check how long it takes a start job to take effect before using it and what happens if it executes between the job completing and getting back in the queue - it takes a long time for complete jobs to reschedule in v7 it appears.
Also with the startjob yuo will still need the loop in case another job comes along before the first completes - you could in fact have several jobs so that several commands could be executed at the same time if you are brave (hence the in process status).
Also with the startjob yuo will still need the loop in case another job comes along before the first completes - you could in fact have several jobs so that several commands could be executed at the same time if you are brave (hence the in process status).
I tried a few tests with sp_start_job and never hd any success at all. Either the command is not running or is waiting a long time to start or something else. I didn't get any error messages, just no joy:
CREATE TRIGGER TR_Inquiry_u
ON INQUIRY
FOR UPDATE
AS
IF UPDATE( nInqStatus ) BEGIN
IF ( SELECT nInqStatus FROM inserted ) = 7 BEGIN
DECLARE @sCmd varchar(256), @sInqIdx varchar(10), @sJobName varchar(256)
SELECT @sInqIdx= nInqIdx FROM inserted
SET @sCmd= 'echo>c:\mce\sCmd.txt HI THERE'
-- create a one-step, one-shot job
SET @sJobName= 'RptGen_' + @sInqIdx
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name= @sJobName ) BEGIN
EXEC msdb.dbo.sp_add_job @job_name= @sJobName,
@enabled= 0, /* exec only on request */
@delete_level= 3, /* delete when done */
@description= 'Runs RptGen',
@owner_login_name= 'SA'
EXEC msdb.dbo.sp_add_jobstep @job_name= @sJobName,
@step_id= 1,
@step_name= 'gen rpt',
@subsystem= 'CMDEXEC',
@command= @sCmd
EXEC msdb.dbo.sp_start_job @job_name= @sJobName
END
END
END
GO
Even when I do it ad-hoc (not in the trigger), I'm getting no joy. I suppose that I need to have the SQL Server Agent running (and I did in several tests, with no obvious success -- sCmd.txt was not created). Where is the success/failure/error code of these jobs recorded?
Anyway, if I need to have the Server Agent running, it sort of defeats my purpose: I *already* have a separate process polling for changes and doing the work. I was looking for an "A happened, so do B" sort of solution.
I have not tried implementing the CmdQueue idea so that is next. I always hesitate before adding a new table and its associated potential maintenance.
Damn, this is turning out to be a pain. I'll keep trying different things as I get time. If you have any more tips to help keep my head above water, I'll appreciate them.
-- Dan
CREATE TRIGGER TR_Inquiry_u
ON INQUIRY
FOR UPDATE
AS
IF UPDATE( nInqStatus ) BEGIN
IF ( SELECT nInqStatus FROM inserted ) = 7 BEGIN
DECLARE @sCmd varchar(256), @sInqIdx varchar(10), @sJobName varchar(256)
SELECT @sInqIdx= nInqIdx FROM inserted
SET @sCmd= 'echo>c:\mce\sCmd.txt HI THERE'
-- create a one-step, one-shot job
SET @sJobName= 'RptGen_' + @sInqIdx
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name= @sJobName ) BEGIN
EXEC msdb.dbo.sp_add_job @job_name= @sJobName,
@enabled= 0, /* exec only on request */
@delete_level= 3, /* delete when done */
@description= 'Runs RptGen',
@owner_login_name= 'SA'
EXEC msdb.dbo.sp_add_jobstep @job_name= @sJobName,
@step_id= 1,
@step_name= 'gen rpt',
@subsystem= 'CMDEXEC',
@command= @sCmd
EXEC msdb.dbo.sp_start_job @job_name= @sJobName
END
END
END
GO
Even when I do it ad-hoc (not in the trigger), I'm getting no joy. I suppose that I need to have the SQL Server Agent running (and I did in several tests, with no obvious success -- sCmd.txt was not created). Where is the success/failure/error code of these jobs recorded?
Anyway, if I need to have the Server Agent running, it sort of defeats my purpose: I *already* have a separate process polling for changes and doing the work. I was looking for an "A happened, so do B" sort of solution.
I have not tried implementing the CmdQueue idea so that is next. I always hesitate before adding a new table and its associated potential maintenance.
Damn, this is turning out to be a pain. I'll keep trying different things as I get time. If you have any more tips to help keep my head above water, I'll appreciate them.
-- Dan
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Featured Post
ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.
One of a set of tools we're offering as a way to say thank you for being a part of the community.
Tackle projects and never again get stuck behind a technical roadblock.
Join Now
You could try crating a bound connection for the app but that sounds like getting too cmoplicated.
Easiest way is proably to put the call to the exe into a table and end the trigger (triggers should be short for the reasons you have found).
Then have a scheduled task that runs an SP that looks at this table and executes any commands in it. You can schedule the SP up to 1 min intervals - if you need it to run more quickly then schedule an SP with a delay statement in a loop that always runs.