SEO

#1 (**permalink**) 02-28-2008, 07:44 PM

Whenever something is inserted to a given table, I want to run some
shell commands using xp_cmdshell. Would it be a bad idea to put this
xp_cmdshell in the INSERT trigger of this table?

I understand that when using xp_cmdshell, the sql thread in question
waits until xp_cmdshell finishes what it's doing. Does this mean if
my xp_cmdshell call takes 30 seconds, that nobody else can insert to
this table until my xp_cmdshell and rest of the insert trigger finish
up?

The alternative solution seems to be frequent polling of the table in
question; while this isn't really a great solution it would seem to
circumvent a table lock brought about by the INSERT trigger.

Thoughts?

Joel

#2 (**permalink**) 02-28-2008, 07:44 PM

Why not do it as a part of the insert. Make peoples inserts go with a
parameterised stored proc, and call it at the end/middle/beginning...
why do they need direct access?

joelpt@eml.cc (Joel Thornton) wrote in message news:<c190a45a.0401272101.258fbedc@posting.google. com>...
> Whenever something is inserted to a given table, I want to run some
> shell commands using xp_cmdshell. Would it be a bad idea to put this
> xp_cmdshell in the INSERT trigger of this table?
>
> I understand that when using xp_cmdshell, the sql thread in question
> waits until xp_cmdshell finishes what it's doing. Does this mean if
> my xp_cmdshell call takes 30 seconds, that nobody else can insert to
> this table until my xp_cmdshell and rest of the insert trigger finish
> up?
>
> The alternative solution seems to be frequent polling of the table in
> question; while this isn't really a great solution it would seem to
> circumvent a table lock brought about by the INSERT trigger.
>
> Thoughts?
>
> Joel

#3 (**permalink**) 02-28-2008, 07:44 PM

joelpt@eml.cc (Joel Thornton) wrote in message news:<c190a45a.0401272101.258fbedc@posting.google. com>...
> Whenever something is inserted to a given table, I want to run some
> shell commands using xp_cmdshell. Would it be a bad idea to put this
> xp_cmdshell in the INSERT trigger of this table?
>
> I understand that when using xp_cmdshell, the sql thread in question
> waits until xp_cmdshell finishes what it's doing. Does this mean if
> my xp_cmdshell call takes 30 seconds, that nobody else can insert to
> this table until my xp_cmdshell and rest of the insert trigger finish
> up?
>
> The alternative solution seems to be frequent polling of the table in
> question; while this isn't really a great solution it would seem to
> circumvent a table lock brought about by the INSERT trigger.
>
> Thoughts?
>
> Joel

Calling external commands from a trigger is generally considered a bad
idea, for exactly the reason you mention. Anything inside a trigger is
inside a transaction, so you want it to complete as fast as possible,
to prevent blocking. Also, if the process you call never returns at
all, or returns something unexpected, you may have a problem.

One solution is to use the trigger to insert a record into a second
table, then poll that table using a scheduled job which calls your
external program. That way you avoid touching the 'main' table as much
as possible.

Simon

#4 (**permalink**) 02-28-2008, 07:44 PM

"Joel Thornton" <joelpt@eml.cc> wrote in message
news:c190a45a.0401272101.258fbedc@posting.google.c om...
> Whenever something is inserted to a given table, I want to run some
> shell commands using xp_cmdshell. Would it be a bad idea to put this
> xp_cmdshell in the INSERT trigger of this table?
>
> I understand that when using xp_cmdshell, the sql thread in question
> waits until xp_cmdshell finishes what it's doing. Does this mean if
> my xp_cmdshell call takes 30 seconds, that nobody else can insert to
> this table until my xp_cmdshell and rest of the insert trigger finish
> up?

Basically yes, you risk locking others out of your table. And what's worse,
if for some reason the external process hangs, your DB is not basically
locked up.

>
> The alternative solution seems to be frequent polling of the table in
> question; while this isn't really a great solution it would seem to
> circumvent a table lock brought about by the INSERT trigger.

It really depends on what you want to do. Polling is one option. Or as
another poster said, possibly a stored proc.

Perhaps if you explain what exactly you want to do in the command shell that
may help.

>
> Thoughts?
>
> Joel

#5 (**permalink**) 02-28-2008, 07:44 PM

> It really depends on what you want to do. Polling is one option. Or as
> another poster said, possibly a stored proc.
>
> Perhaps if you explain what exactly you want to do in the command shell that
> may help.

It sounds like polling will be the way to go. I would use a stored
procedure for the insert, but the insert is actually being done by a
third-party app which only knows how to insert a record to a table via
ODBC. Correct me if I'm wrong, but I don't think having a secondary
"initial insert" table with trigger would help because I will again be
locking that table until my xp_cmdshell call finishes up.

Thanks for the info. I am probably going to have it poll every 5
seconds so that there is little noticeable lag on the user's side
(which was my motivation for wanting it in the trigger).

Maybe the next incarnation of T-SQL will have fork().

Joel

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Using xp_cmdshell in INSERT trigger: bad idea?