xp_cmdshell from within a trigger on SQL 2000 SP4

[tolcis]

I have a trigger that should be execute on each row insert and only if
appcode = 'I' and datasent = 0. It should execute a DTS package. The
DTS package by itself runs about 6 seconds. Trigger was created
successfuly. When I try to insert a row my db hangs. I can see that
SPID on my db is hang by SPID from master db. It doesn't complete
until I kill that SPID. Why is that?
This is the trigger that I am trying to execute:
CREATE TRIGGER myExport
ON ruExport

FOR INSERT
AS
declare @appcode varchar (10)
select @appcode = appcode from ruexport where appcode = 'I' and
datesent = 0

if @appcode = 'I'
exec master..xp_cmds hell 'DTSRun /S "KOCL384017 SQL" /N "Sys_Export " /G
"{17D112A8-413E-420F-A624-3790BDFBED9F}" /W "0" /E'

go

Any suggestions?

[AlterEgo]

tolcis,

There are a couple of considerations you might want to think about here.

1. Triggers should be designed as set operations. A trigger only fires once
for each batch that is being updated. If the batch updates multiple rows,
then your trigger should account for it. As it is written, your trigger
assumes only one row is getting updated.

2. You are already selecting a set where the appcode = 'I', why the if
@appcode = 'I' comparison later in the trigger?

3. Personal architectural philosophy: I would not execute a DTS package
directly from a trigger. There are many external kinds of issues that can
cause a DTS package to fail. I would make the interaction with the DTS
package more loosely coupled with the application. An alternative would be
to have the DTS package set up a job to run once a minute ( or hour, etc.).
First job step could check to see if any rows have to be processed ( appcode
= 'I' and datesent = 0). If none need to be processed then exit the job
with success, otherwise execute the DTS package.

-- Bill

"tolcis" <ny********@gma il.comwrote in message
news:11******** *************@a 75g2000cwd.goog legroups.com...

>I have a trigger that should be execute on each row insert and only if
appcode = 'I' and datasent = 0. It should execute a DTS package. The
DTS package by itself runs about 6 seconds. Trigger was created
successfuly. When I try to insert a row my db hangs. I can see that
SPID on my db is hang by SPID from master db. It doesn't complete
until I kill that SPID. Why is that?
This is the trigger that I am trying to execute:
CREATE TRIGGER myExport
ON ruExport

FOR INSERT
AS
declare @appcode varchar (10)
select @appcode = appcode from ruexport where appcode = 'I' and
datesent = 0

if @appcode = 'I'
exec master..xp_cmds hell 'DTSRun /S "KOCL384017 SQL" /N "Sys_Export " /G
"{17D112A8-413E-420F-A624-3790BDFBED9F}" /W "0" /E'

go

Any suggestions?

[Dan Guzman]

To add to AlterEgo's response, note that the DTS package is an external
process and not running in the trigger transaction context. If the DTS
package accesses uncommitted data from the INSERT statement, it will be
blocked until the INSERT completes. However, the INSERT can't complete
until the DTS package executed from the trigger completes. This is a
deadlock that requires you to KILL the spid.

A better application design is to have the trigger insert into a queue table
like AlterEgo suggested and have a separate perform the process
asynchronously.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"tolcis" <ny********@gma il.comwrote in message
news:11******** *************@a 75g2000cwd.goog legroups.com...

>I have a trigger that should be execute on each row insert and only if
appcode = 'I' and datasent = 0. It should execute a DTS package. The
DTS package by itself runs about 6 seconds. Trigger was created
successfuly. When I try to insert a row my db hangs. I can see that
SPID on my db is hang by SPID from master db. It doesn't complete
until I kill that SPID. Why is that?
This is the trigger that I am trying to execute:
CREATE TRIGGER myExport
ON ruExport

FOR INSERT
AS
declare @appcode varchar (10)
select @appcode = appcode from ruexport where appcode = 'I' and
datesent = 0

if @appcode = 'I'
exec master..xp_cmds hell 'DTSRun /S "KOCL384017 SQL" /N "Sys_Export " /G
"{17D112A8-413E-420F-A624-3790BDFBED9F}" /W "0" /E'

go

Any suggestions?