Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2003
    Location
    Oxfordshire
    Posts
    18

    Unhappy Unanswered: 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."

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    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

  3. #3
    Join Date
    Aug 2003
    Location
    Oxfordshire
    Posts
    18
    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'

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    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.

  6. #6
    Join Date
    Aug 2003
    Location
    Oxfordshire
    Posts
    18
    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.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jun 2003
    Location
    Sydney, Australia
    Posts
    66
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •