Run a job with a trigger 1

[puppet]

Is it possible to create an insert trigger that calls a job that is stored on the SQL server?

 

[bperry]

I guess you could probably use the stored procedure sp_start_job.

EXEC sp_start_job @job_name = 'MyTriggerJob'

You would probably want to test this, to make sure that the execution of the command (i.e. the launching of the job) takes place pretty quickly and doesn't slow the total trigger time down too much.


--------------------
bperry

 

[bperry]

Hi again,
I just tried a little test myself to make sure that suggestin worked. I noticed this:

[ol]
[li]Within the trigger, you probably have to fully qualify the startjob command like this:

EXEC msdb..sp_start_job @job_name = 'MyTrigJob'

[li] If the ServerAgent process is running, everything proceeds fine. But if ServerAgent is not running, it seems that (a) the Insert takes place, (b) an error is thrown indicating that the job cannot start, and (c) the Insert is not rolled back.

At least that's what happens on my system. You should test your situation to make the behavior is satisfactory to you.
[/ol]

 

[bperry]

Hmmmmm, I just did another test, and found another problem or issue to consider:

If you do two (or more) Inserts at once, say like this:

insert tblTest1 VALUES ('billy')
insert tblTest1 VALUES ('bully')

I believe this causes the trigger to fire twice. And what I find happens is that the first trigger fire starts up the job okay; but the second trigger fire fails because the job is already running. (However, I found that both records are still inserted successfully, and are not rolled back.)

Even if you don't do two inserts at once, as I have shown, I think you still have the same problem with multiple users. i.e. first user does Insert, fires trigger, starts job. second user, a nano-second later, does Insert, fires trigger, job fails because job is already running from first user.

So, I don't know if you can live with that kind of behavior or not. I'm starting to get the feeling it may not be a great idea to run a job from a trigger. Hopefully, other forum readers will post their experience ot comments here.

Sorry for the multiple posts.
bperry

 

[bperry]

Hello once more!

In earlier posts, I mentioned 2 problems or issues I had found when starting a job from your Insert trigger:
(1) What to do if SQLSERVERAGENT not started?
(2) What to do when multiple Inserts occur, either by the same user or by near-simultaneous Inserts from multiple users.

I have found what seems to be a solution to the first issue, if that is of any interest at all to you.
------------------------------------------------
We can create a stored procedure that checks to see if the ServerAgent is running. You then call that SP from within your trigger, and then decide what to do based on the result of the check.

First of all, here is the stored procedure itself:
[tt]
CREATE PROCEDURE IsAgentRunning
AS
SET NOCOUNT ON
CREATE TABLE #TempTable (
OutputColumn varchar(256)
)
-------------------------------------------------------
--Use either Method One
-------------------------------------------------------
/*
INSERT INTO #TempTable
EXEC
master..xp_servicecontrol QueryState, SQLServerAgent

If EXISTS (Select * From #TempTable
Where OutputColumn Like '%running%')
Return 1
Else
Return 0
*/
-------------------------------------------------------
-- or Method Two
-------------------------------------------------------
INSERT INTO #TempTable
EXEC master..xp_cmdshell 'NET START'

If EXISTS (Select * From #TempTable
Where OutputColumn
Like '%SQLSERVERAGENT%')
Return 1
Else
Return
GO
[/tt]
-----------------------------------------------------
Th SP above shows two methods for actually performing the test. Both seem to work equally well on my system; but you don't need both, so just pick one method and either comment out or delete the other from the SP.
********************************
Now, to actually user this new SP in your trigger, you do something like this:
[tt]
CREATE TRIGGER tblTestInsTrig ON dbo.tblTest
FOR INSERT
AS
declare @IsRunning int
execute @IsRunning = IsAgentRunning
IF @IsRunning = 1
EXEC msdb..sp_start_job @job_name = 'MyTriggerJob'
[/tt]

If you would like to cancel the Insert altogether if the ServerAgent is not running, then you could do this:

[tt]
CREATE TRIGGER tblTestInsTrig ON dbo.tblTest
FOR INSERT
AS
declare @IsRunning int
execute @IsRunning = IsAgentRunning
IF @IsRunning = 1
EXEC msdb..sp_start_job @job_name = 'MyTriggerJob'
ELSE
ROLLBACK
[/tt]

-----------------------------------------------
So that should solve the problem of determining whether the ServerAgent is running or not. But the issue about multiple Inserts is still outstanding.

bperry

 

[bperry]

My apologies, there was a very minor typo in the code for the stored procedure above. In Method 2 in the SP, a Return value was accidentally left out.
[tt]
------------------
-- or Method Two
------------------
INSERT INTO #TempTable
EXEC master..xp_cmdshell 'NET START'

If EXISTS (Select * From #TempTable
Where OutputColumn
Like '%SQLSERVERAGENT%')
Return 1
Else
Return 0
GO
[/tt]
-----------------
Sorry about that.

 

[puppet]

Thanks for all your time on this - I will have a chance to play with this later today and will let you know how I get on.

 

[puppet]

OK, I've had some time to think about this a bit more and I think I'm trying to do this the wrong way but I don't know how I should be doing it. Here is my whole situation:

I have a user who works in MS Access on a view which is stored in MS SQL server. The view is of a few tables which are updated daily by several dts packages which import data from an AS400. Once the data is imported two jobs are run to update the afore mentioned tables with the imported information (basically to consolidate a lot of tables into three core tables).

Occasionally my user needs to manually run the update. I have created a batch file which will run the dts packages but without giving her access to SQL enterprise manager - or Query Analyzer if at all possible - I don't know how to run the two final jobs to complete the update.

What I am thinking now is some kind of way to trigger the jobs - either with an insert trigger or is there some way to run a job from MS access? Or a stored proceedure even?

Wow - long explaination - any help appreciated!

 

[bperry]

Okay. I don't think the trigger/startjob stuff is the way to go, but it was interesting looking into that anyway. Might come in handy sometime.


Someone in this forum who has worked in Access may know to call the SP. Two suggestions:

(1) Start a new thread in this forum with an appropriate subject 'Run stored procedure from Access'. i.e. so readers won't be mislead by our current subject re: jobs.

(2) Consider posting the problem in one of the Access forums; those people must have solved this problem many times before.


I have seen Access code that looks like this: doesn't mean anything to me, but it may to you.

Dim cnn as ADODB.Connection
Dim cmd As NEW ADODB.Command
Dim pm As New ADODB.Parameter

Set cnn = currentProject.Connection
With cmd
.ActiveConnection = cnn
.Commandtype = adCmdStoredProc
.CommandText = "YourProc"
.Parameters.Add (...)
.Execute
End With
-----------------------------------

 

[travisbrown]

Brian, you sent me a link to your very helpful SQL homepage once. I lost all my favorites since then. Could you send me the link again? Thanks.

travis.brown@bchydro.bc.ca

 

[SQLSister]

I'd go the stored procedure route myself. Put an update records button on your form and use code simliar to that above to call the stored procedure or use a passthrough query and call that from the on click event of the update records button.

A trigger would be extremely danfgerous in this situation, you don't want to reimport the data every time someone adds data and it sounds like you don't actually want to manually add data anyway.