UnTangling T-SQL Execution Sequence

Posted on 2005-05-04
Last Modified: 2012-08-13
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.

Question by:Danimal
    LVL 1

    Author Comment

    **sigh**  Hoping for a late-night solution
    LVL 23

    Accepted Solution

    Calling a DTS package from a trigger is not really a good idea. If everytime data is inserted into this table and the trigger has to fire off the DTS package, the system will more than likely perform slowly. The speed of inserts will be a lot slower.

    I'd suggest, instead of calling the DTS direct from the trigger, set up some form of job queue.
    Pseudo-code inside the trigger:
    1. Insert records into appropriate SQL Server tables
    2. Insert a record into new table "DTSQueue" (just store "the weird T-SQL command" in a varchar column, a timestamp and a Completed bit field = 0)

    Now, schedule a job to run as often as required (e.g. every minute) to look at this DTSQueue table to see if the DTS package needs to be executed (search for Completed = 0). If so, execute the T-SQL command and when complete set the Completed flag = 1.

    Does this make sense? Hopefully it will give you an idea of an alternative approach. The possible downside of this is that the DTS package would not be run instantly (there could be a delay of up to 1 minute)
    LVL 1

    Author Comment

    adathelad, this sounds like it will work fine.. except one thing.. how do you schedule a job to run every minute?  I looked at the scheduler for DTS packages.  It looks like the most frequently you can schedule them is daily..

    also, why put the T-SQL command for the DTS package in varchar field?  I don't understand that.

    Thanks for the help...

    LVL 23

    Expert Comment

    >> how do you schedule a job to run every minute?  <<
    Right click the DTS package -> Schedule
    In the popup box, select "Daily" in the "Occurs" box.
    You can then set the daily frequency underneath (check the  "Occurs every..." option) where you can choose to run every x hours or minutes

    >> why put the T-SQL command for the DTS package in varchar field? <<
    Good question, and you probably don't need to. The reason I suggested it is because it would then be a generic solution - i.e. if you had 5 DTS packages to run, you just enter the T-SQL command for the one you are "queueing". It's generic as you can use the same approach for any DTS package you may need to queue.

    Hope this helps
    LVL 1

    Author Comment

    adathelad, I will try that Monday.  When I get it working, I will award points to you.  If there is troubleshooting you can help me with, I will boost points.  

    It is sooooooo key that you can help me with this.  I have tried this before and had problems.  It seems like it should be easy.  I turned on SQL Server Agent, did the scheduling more or less as you indicated, and for reasons unknown to me, the packages would not run.  I run the packages daily by right-clicking in Enterprise Manager, hitting execute, but via the scheduler, I have had no luck.
    LVL 1

    Author Comment

    I still have not had a chance to work on getting that DTS scheduler to work.  Hopefully later today I can do that.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now