Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2007
    Posts
    15

    Unanswered: SOLVED: SQL to email when table is updated.

    I want to setup a method to get emailed whenever someone adds an item to a particular table.

    I'm looking at SP's, and DTS's.. but i'm just not sure what the best path is.

    http://www.sqldev.net/dts/SMTPTask.htm will create a great Custom DTS Email Task.. but then i don't know what to do next... I don't know how to say.. "If a new row is added, then run this DTS"... or if i should schedule the DTS to run every 30 minutes and then somehow query for new "Created" dates.

    Please Help!
    Last edited by simp1eton; 08-09-07 at 14:32.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    you could do this from a trigger but it is not such a good idea.

    stick with the DTS package. Make a job that polls the table every once in a while and if it finds the right data, fire the DTS package from the job.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Apr 2007
    Posts
    15
    Thanks Sean,

    How would I write that as a DTS package?
    Don't I need to use some kind of conditional IF statement or something?

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    your job would look something like so...

    Code:
    if exists(select * from MyTable where Createddate between dateadd(mi,-15,GetDate() and GETDATE())
    BEGIN
         xp_cmdshell('dtsrun <INSERT SYNTAX FOR DTSRUN parameters>')
    END
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Apr 2007
    Posts
    15
    Are you saying to create a DTS Job that executes that TSQL command, which calls the SP to run the Email DTS ?

    I'm a little confused.

  6. #6
    Join Date
    Apr 2007
    Posts
    15
    Thanks for your help,
    I'm up and working great now...
    Here's what I ended up doing:

    1. Installed DTS custom app/task: "SMTPTask.exe" http://www.sqldev.net/dts/SMTPTask.htm
    2. Created DTS "MIS_EMAILER", added New SMTPTask, and customized it
    3. Told the message body to pull from: D:\MISRequest_autoemail\New Ticket Export.txt
    4. Created a SQL "TSQL" Job with the logic of: "If assigned=0 in newrequest table, then bcp a query out to "New Ticket.txt" file, and then run the DTS "MIS_EMAILER"
    5. Scheduled this task to run every hour from 6am - 5pm

    Code:
    IF EXISTS (select * from newrequest where assigned=0) 
    BEGIN declare @cmd varchar(1000) select @cmd = 'bcp "select workreq, submittedby, request1, comment FROM MISRequest..newrequest where assigned=0" queryout "D:\MISRequest_autoemail\New Ticket Export.txt" -T -c'
    EXEC master..xp_cmdshell @cmd
    EXEC master.dbo.xp_cmdshell 'dtsrun -E -S"Servername" -N"MIS_EMAILER"'
    END

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •