Launch exe using trigger within DTS

The following has been posted on another thread and I would like to know if anyone can tell me if and how this could be done. I'm a complete novice when it come to DTS.

TIA.

"I would investigate having the .exe be part of a "job"
under DTS Local Packages and having the trigger start
that job - not really sure if this is possible."You can start an exe via
exec master..xp_cmdshell 'xxxx.exe'

You can start a dts package in the same way by using dtsrun.exe if you really want to use dts - but that would be starting an app to run an app.

I would not advise doing this from a trigger though. Better to put the request into a table and have a scheduled task run the exe.

Note that the exe must not have any user interaction as there is no display or input stream attached|||Originally posted by nigelrivett
You can start an exe via
exec master..xp_cmdshell 'xxxx.exe'



Thanks Nigel

Do I just add this at the end of the trigger as per below ? Pretty new to this stuff !

CREATE TRIGGER tr_owner_coord_email ON dbo.ownership
after insert as
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
update ownership
SET coordemail = (select con.emailaddress
from contact as con with (nolock)
where coordinator = (con.forename + ' ' + con.surname))

exec master..xp_cmdshell 'xxxx.exe'|||Originally posted by nigelrivett
I would not advise doing this from a trigger though. Better to put the request into a table and have a scheduled task run the exe.


Take Nigels advice...

Do you know what a trigger does?

If you insert 1000 rows, it will kick off 1000 times...

Is that what you want?

Also you're not referencing the virtual inserted table...

You could probably make your update a default contraint...|||Originally posted by Brett Kaiser
Take Nigels advice...

Do you know what a trigger does?

If you insert 1000 rows, it will kick off 1000 times...

Is that what you want?

Also you're not referencing the virtual inserted table...

You could probably make your update a default contraint...

If you insert 1000 rows at once - trigger will fire once, if row by row - trigger will fire for every insert.|||There is not going to be heavy demand with the trigger. It will probably only be average of 60-80 rows inserted per day.

The other way I was handling this was with a timer in Vb but max interval on that is 60 seconds so even more overhead.

I would prefer to do it as Nigel suggested but need more detailed info being a newbie. I just wanted to know if there is a way the exe can be launched after the trigger had been executed. I am open to all suggestions and as was put in the original post someone thought a DTS package could launch the exe after the trigger. That way it would only be run 60-80 times per day rather than over 500 times pd using vb timer.|||Originally posted by snail
If you insert 1000 rows at once - trigger will fire once, if row by row - trigger will fire for every insert.

So True....my bad...|||PMJI, be careful withn the trigger.

While the trigger runs, the transaction is not yet commited. The database keeps exclusive locks on the row or page you are about to insert into. If you have a multi-statement transaction, then there are even more locks, preventing everyone else to access your records.

Now imagine you put some long-running command in the trigger. AFAIK, xp_cmdshell runs synchronously, i.e. it waits untill your EXE completes. All the while your records are locked. You may not worry about concurrency, if this is a single-user application or some background drip-feed.

But if you intend to connect from the EXE back into Sql Server, then that is a separate connection. The EXE won't see the record just being inserted by the trigger. If the EXE attempts to Select one of the locked records, you may end up waiting forever ( Exe waits for Trigger to release the locks, while Trigger waits for Exe to complete ). Sounds like fun.

As about integrity: it is nice to know you can roll back a multi-statement transaction, including the efefcts of triggers. But xp_cmdshell is outside the Rbdms, so it won't roll back at all.

I realise triggers are an attractive thought, to streamline the chain of command, and to reduce the number of moving parts. In practice, they don't perform this role too well.

Andrew Schonberger