|
Posted by Dan Guzman on 01/20/07 16:57
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" <nytollydba@gmail.com> wrote in message
news:1169229052.442503.88100@a75g2000cwd.googlegroups.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_cmdshell 'DTSRun /S "KOCL384017SQL" /N "Sys_Export" /G
> "{17D112A8-413E-420F-A624-3790BDFBED9F}" /W "0" /E'
>
> go
>
> Any suggestions?
>
Navigation:
[Reply to this message]
|