I wish to send results of 'query' from trigger by email to specific recipients

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » I wish to send results of 'query' from...

15 posts, Page 1 of 2

1 2 »»

I wish to send results of 'query' from trigger by email to specific recipients

Rate Topic

Display Mode

Topic Options

Author

Message

mschi6317

Posted Wednesday, December 02, 2009 8:04 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, January 25, 2010 11:29 AM
Points: 7, Visits: 12

ALTER TRIGGER [trig_NO_LABOR_DATA]
ON [dbo].[WIP_FG]
AFTER INSERT
AS
--insert results of query into the table below

INSERT INTO No_Prod_Data_Inn (job_number, fg_date, qty_scanned)

--the below query identifies those jobs scanned to finished goods without
--any pde/wip transactions

SELECT wip_fg.job_number, wip_fg.trx_datetime, wip_fg.trx_qty
FROM INSERTED wip_fg inner join spec_file on wip_fg.item_no=spec_file.item_no
left join wip_mach_ops
on wip_fg.job_number=wip_mach_ops.job_number
where wip_fg.job_number>700000
and wip_mach_ops.job_number IS NULL

--END
--try to email results of query here or at very least notify production
--there is a job scanned with no pde/wip data

How can I sent results of insert values (Table No_Prod_Data_Inn) to specific recipients.

Post #827498

GSquared

Posted Wednesday, December 02, 2009 8:15 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569

Personally, I'd cache the query results in a table, and have a job periodically check the table and send any results it finds. That way, you're not slowing down the trigger (which slows down the whole transaction).

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #827516

homebrew01

Posted Wednesday, December 02, 2009 8:16 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 9:03 PM
Points: 2,551, Visits: 7,204

I agree about not putting the email in a trigger.

Here's the email syntax you can run from a job:

EXEC msdb.dbo.sp_send_dbmail
    @recipients = 'MyName@MyCompany.com',
    @query = 'SELECT wip_fg.job_number, wip_fg.trx_datetime, wip_fg.trx_qty 
FROM INSERTED wip_fg inner join spec_file on wip_fg.item_no=spec_file.item_no
left join wip_mach_ops
on wip_fg.job_number=wip_mach_ops.job_number
where wip_fg.job_number>700000
and wip_mach_ops.job_number IS NULL
' ,
    @subject = 'job scanned with no pde/wip data'

Post #827517

mschi6317

Posted Wednesday, December 02, 2009 9:07 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, January 25, 2010 11:29 AM
Points: 7, Visits: 12

My only issue with that is I can do that from Crystal. I would like to send a message to intended recipeints at time of insert. However, I suppose I could set up a job to fire several times/day.

Post #827592

mschi6317

Posted Wednesday, December 02, 2009 9:10 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, January 25, 2010 11:29 AM
Points: 7, Visits: 12

Thanks, very much for the help. Although it's probably better to send results from 'cached' table, I may still include this code in the trigger. I'd like it to send a message at time of insert. Thanks again for your help!

Post #827595

mschi6317

Posted Thursday, December 03, 2009 9:39 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, January 25, 2010 11:29 AM
Points: 7, Visits: 12

Thanks again for your help yesterday. I received an error when trying to execute this query stating @profile name was needed, so I added the profile as configure in database mail. I then tried to execute query again and received a different error. My new query looks like this;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = my profile name',
@recipients = 'myemail@comapny.com',
@query = 'SELECT wip_fg.job_number, wip_fg.trx_datetime, wip_fg.trx_qty
FROM INSERTED wip_fg inner join spec_file on wip_fg.item_no=spec_file.item_no
left join wip_mach_ops
on wip_fg.job_number=wip_mach_ops.job_number
where wip_fg.job_number>700000
and wip_mach_ops.job_number IS NULL
' ,
@subject = 'job scanned with no pde/wip data'

after execution I receive the following error;

Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 476
Query execution failed: Msg 208, Level 16, State 1, Server ILLT1394, Line 1
Invalid object name 'INSERTED'.
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

trying to find a solution currently....if you have any thoughts please let me know...thanks

Post #828312

Elliott Whitlow

Posted Thursday, December 03, 2009 9:57 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 12:02 PM
Points: 5,854, Visits: 4,873

Inserted is only available in the trigger. I don't even think it can be used in a sub-sproc. The query there is not executed in the same context or even at the same time that the trigger is running. DB Mail uses service broker to queue up the email and then spools up the dbmail executable to do the work. Short answer, you can't use the inserted "table" in this way. Also you mentioned profile, do you have a defined public default profile in DB Mail, if not you need one if you aren't going to tell it what profile to use.

CEWII

Post #828327

Jonathan Kehayias

Posted Thursday, December 03, 2009 10:12 AM

SSCommitted

Group: General Forum Members
Last Login: Sunday, May 12, 2013 4:26 PM
Points: 1,696, Visits: 1,742

Rather than do this in a trigger, I'd probably go the route of using a OUTPUT clause to get the inserted information and the pop the necessary information into a broker queue to let it process asynchronously to prevent impacting performance in a trigger.

Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs

Post #828348

mschi6317

Posted Thursday, December 03, 2009 12:25 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, January 25, 2010 11:29 AM
Points: 7, Visits: 12

Yes I am finding it difficult to get the '2nd' process of shooting off email after insert to work. I was successful in creating profile and can send results from inserted table, but I really wanted to alert production as soon as the insert happened. I supposed I could setup a job as was suggested to run several times/day, etc. Thanks again.

Post #828426

Jonathan Kehayias

Posted Thursday, December 03, 2009 12:39 PM

SSCommitted

Group: General Forum Members
Last Login: Sunday, May 12, 2013 4:26 PM
Points: 1,696, Visits: 1,742

This sounds like a process issue to me. When the data gets inserted where is it coming from? An Application, a Adhoc user request, a bulk upload from a flat file? Any of those three sources could generate the notification to production that a insert occured as well.

Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs

Post #828439

« Prev Topic | Next Topic »

15 posts, Page 1 of 2

1 2 »»

Permissions