Trigger and DTS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Trigger and DTS

Author

Topic

mufasa
Yak Posting Veteran

62 Posts

Posted - 2003-10-02 : 10:21:19

I have a table which gets updated periodically, (once maybe twice a week). I made a DTS package that creates a table in a second SQL instance database.

Instead of scheduling this package everyday, I wanted a trigger to run it everytime data is modified.

Never used triggers yet, and can't seem to find a way to run a package from a trigger.

Thanks for any help
Mufasa

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-10-02 : 10:31:31

There are two ways to execute a DTS package via T-SQL.
1.) use xp_cmdshell to run the DTSRun.exe command line utility to execute the package.
2.) create a job (without an enabled schedule) that runs DTSRun.exe and use sp_start_job to start the job from T-SQL.

I'd imagine that from a trigger, you'd want to use option 2. xp_cmdshell will not return until the dtspackage is completed and thus will hold up the transaction in your original DML that caused the trigger to fire.

Jay White
{0}

mufasa
Yak Posting Veteran

62 Posts

Posted - 2003-10-02 : 11:25:27

Hi

Your idea worked except for one thing.
I can not use "USE" command in a trigger or Stored Proc
Since jobs are handled under msdb database the command does not work in the trigger of a different database.

The command workes ok in the query analyzer under msdb database.

Is there a way around this problem,

Thanks
Mufasa

mufasa
Yak Posting Veteran

62 Posts

Posted - 2003-10-02 : 12:36:35

Finally figured it out
I created the SP

CREATE PROCEDURE [dbo].[trafic] AS
EXEC sp_start_job @job_name = 'Traffic'
GO

I then created the trigger in the Database Report

CREATE TRIGGER [updateCCTD] ON [dbo].[His_Day_TraficCounter]
FOR INSERT, UPDATE, DELETE
AS EXEC msdb.dbo.trafic

Work fine
Thanks

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-10-02 : 14:16:41

ok, you lost me. When did I say to use the "USE" keyword? or did you misinterpret my comment "use sp_blahblah" as a literal? I meant use it, call it, execute it. And whats wrong with using (shit, there I go again) the 3-part name directly in the trigger?

Jay White
{0}

mufasa
Yak Posting Veteran

62 Posts

Posted - 2003-10-02 : 14:31:45

quote:
Originally posted by Page47

ok, you lost me. When did I say to use the "USE" keyword?

As I had mentioned, I have not used triggers before, and you did not tell me to use "USE", that I found in Books Online example.

But you got me on the right track, and now it works.

I learnt a few new things on the way.

Thanks
Mufasa

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources