Call external program from a stored proc / trigger

What is the stored proc that can call an external exe, etc. from a stored
procedure, function, trigger, etc.?
I can not for the life of me remember what it is. I need to an action upon
row inserts and wanted to have a trigger run an external program.
thanks,master..xp_cmdshell
AMB
"aiKeith" wrote:

> What is the stored proc that can call an external exe, etc. from a stored
> procedure, function, trigger, etc.?
> I can not for the life of me remember what it is. I need to an action upo
n
> row inserts and wanted to have a trigger run an external program.
> thanks,
>|||Hi
You can call xp_cmdshell, but it is ill advised.
What happens if your trigger fires again before the application finishes?
If the external application fails, the error could rollback the DML done.
The locks aquired by the DML, remain in force until the SP or Trigger
completes. This can cuase concurrency problems.
Regards
Mike
"aiKeith" wrote:

> What is the stored proc that can call an external exe, etc. from a stored
> procedure, function, trigger, etc.?
> I can not for the life of me remember what it is. I need to an action upo
n
> row inserts and wanted to have a trigger run an external program.
> thanks,
>|||Thank you Alej...
I believe there is a specific procedure for calling external applications /
dll's, etc. I even had a sample that called a C++ applications dll, but i
cannot find it. I did not want to use the cmd shell.
Any ideas?
"Alejandro Mesa" wrote:
> master..xp_cmdshell
>
> AMB
> "aiKeith" wrote:
>|||Hi
It is considered a bad idea to run .EXE from a trigger. Look , when a
trigger gets fired it opens a new transaction and keeps it open till all
commands will be completed succesfuly. If SQL Server raises an error inside
the trigger all transactions should be rollbacked which may cause to
locks,blocking or even deadlocks. Do you really ,really need it to be ran
from the trigger?
"aiKeith" <aiKeith@.discussions.microsoft.com> wrote in message
news:A067E30C-E88E-40D2-AD5D-181FFB98B664@.microsoft.com...
> What is the stored proc that can call an external exe, etc. from a stored
> procedure, function, trigger, etc.?
> I can not for the life of me remember what it is. I need to an action
upon
> row inserts and wanted to have a trigger run an external program.
> thanks,
>|||sp_OA*
AMB
"aiKeith" wrote:
> Thank you Alej...
> I believe there is a specific procedure for calling external applications
/
> dll's, etc. I even had a sample that called a C++ applications dll, but i
> cannot find it. I did not want to use the cmd shell.
> Any ideas?
> "Alejandro Mesa" wrote:
>|||You probably want the sp_OA procs. Lookup sp_oacreate in Books Online.
As Mike indicated, this is a really bad idea in a trigger. One issue is
how to handle a transaction that rolls back. Since there is no built-in
functionality for that you would have to detect any error condition and
call your external process again to rollback its action. You therefore
gain little from doing it in a trigger (why not just call your code
once only, at the end).
On the other hand if you don't need this to be part of an atomic
transaction then there is no need to put it in a trigger at all. Best
bet is to create an external process, outside of SQL Server, that
regularly polls the table for changes.
David Portas
SQL Server MVP
--|||Thank you all for your great comments.
I am aware that calling code from a trigger is typically bad practice, and I
almost included that in the first post.
This is only for a temporary thing... the reason I want to do it in a
trigger is:
1.) Less resources... I dont know if its going to run today, next w or
not for a month, therefore I dont want something constantly monitoring the d
b
(even if its once a day)
2.) I can easily disable/remove the trigger.
Thanks again, you guys inspire me to answer posts.
"Uri Dimant" wrote:

> Hi
> It is considered a bad idea to run .EXE from a trigger. Look , when a
> trigger gets fired it opens a new transaction and keeps it open till all
> commands will be completed succesfuly. If SQL Server raises an error insid
e
> the trigger all transactions should be rollbacked which may cause to
> locks,blocking or even deadlocks. Do you really ,really need it to be ran
> from the trigger?
>
>
> "aiKeith" <aiKeith@.discussions.microsoft.com> wrote in message
> news:A067E30C-E88E-40D2-AD5D-181FFB98B664@.microsoft.com...
> upon
>
>