UnTangling T-SQL Execution Sequence
Posted on 2005-05-04
STEP 1: I have a trigger that inserts records into a few tables.
STEP 2: After that, I need the trigger to fire a DTS package, that
inserts a record into a file/table on an AS400.
The trigger in step 1 works. After the trigger has executed,
I can fire the DTS package in from Query Analyzer, by
exec master..xp_cmdshell 'dtsrun /Sourservername /UadminOnServer /Padminpassword /NmyPackageName' -- weird T-SQL command
When I put the statement for firing the DTS package into the trigger, it crashes SQL Server.
I have to stop/start the MSSQLSERVER service.
When the DTS firing is in the trigger script, I am thinking SQL Server tries
running the table updates and the DTS package at the same time, instead
of finishing the table updates, then starting DTS.
How do I make the trigger do this:
1. Insert records into tables in SQL Server
2. AFTER 1 is DONE, execute the DTS package with that weird T-SQL command noted above.
As always, any help is appreciated. I have a meeting with the big boss in the AM, would like
to have this as slick as possible.