Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 273
  • Last Modified:

UnTangling T-SQL Execution Sequence

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.

  • 4
  • 2
1 Solution
DanimalAuthor Commented:
**sigh**  Hoping for a late-night 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)
DanimalAuthor Commented:
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...

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

>> 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
DanimalAuthor Commented:
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.
DanimalAuthor Commented:
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now